Loan Amortization For Monthly-Rest Installment Loan

This blog will teach you how to work out a rough schedule on working out a monthly-rest installment loan. A monthly-rest loan is a loan where the principal amount is reduced on a monthly basis. To work out a schedule for this type of loan, you must first work out how much is the monthly installments. To do this, open a spreadsheet and do the following below. Remember to follow each step as it says or the formula will not work.

Suppose if you would like to apply for a two-year loan of $8,000 at 10% per annum. To work out the monthly installment amount:
1) In Cell A1, type " Monthly-rest Installment Loan"
2) In A3, type "Amount of Loan".
3) In B1, type "8,000"
4) In A4, type "Interest Rate per year"
5) In B4, type "10%"
6) In A5, type "No. of Installments per year."
7) In B5, type "12"
8) In A6, type "No of years of loan"
9) In B6, type "2"
10) In A7, type "Interest rate per month"
11) In B7, type "=B4/B5"
12) In A8, type "Total number of installments"
13) In B8, type "B6*B5"
14) In A10, type "Amount per installment ="
15) In B10 , type "=PMT(B7,B8,-B3,0,0)"

The amount that you would have to pay for the installment is $369.16 per month.

To work out the loan amortization schedule, in the same spreadsheet, do the following:
1) In D1, type "Loan Amortization Schedule"
2) In E2 , type "Principal at beginning of month"
3) In F2, type "Interest due at end of month"
4) In G2, type "Installment Payment"
5) In H2, type "Principal Repaid"
6) In D3, type "=1"
7) In E3, type "B3"
8) In F3, type "$B$7*E3". Copy this formula from F4 to F26
9) In G3, type "=B10"
10) In H3, type "G3-F3". Copy this formula from H4 to H26
11) In D4 to D26, type 2 all the way to 24
12) In E4, type "E3-H3". Copy this formula from E5 to E26
13) In G4, type "=G3" and copy this formula from G4 to G24

The final result will look something like this:

You may change the figures in B3, B4 and B6 to see how this schedule changes together with the monthly payments payable. If you have to change the number of years, don’t forget to change the column on the number of payments to (12 * number of years).

Disclaimer: Please note that aside from the schedule above, there might be other charges by the bank such as admin fee, processing fee etc. Do check with your bank officer or financial planner before making any decision to go ahead with any bank loans.

In the next blog, we will discuss annual -rest loan schedules. See you around when I post my next blog.

Time Value of Money : Future Values

In this blog, we'll explore how to use a spreadsheet to calculate future values without dealing with any complicating formulas. Just follow the instructions step by step and you will never go wrong.

Future Value of a Single Payment

1) In A1, type "Interest Rate Per Year"
2) In A2, type "No of years"
3) In A3, type "Value In the present day"
4) In A5, type " Future Value = "
5) In B5, type "= FV(B1,B2,0,B3)"

Suppose if you would like to put $10,000 into a one year fixed deposit that pays an interest of 5%. To find the future value of this deposit i.e, the value of the deposit on the day the fixed deposit matures, do the following:

1) Type 5% in B1
2) Put 1 in B2
3) In B3, type -$10,000

The future value will be reflected as $10,500. The final result will look like this:

Try changing the values in B1, B2 and B3 to watch how the future value changes.

Future Value of a series of payments

To find the future value of a series of payments, do the following:
1) In A1, type "Interest Rate Per Year"
2) In C1, type "If you're workign with months, divide this figure by 12)
3) In A2, type "No of years"
4) In C2, type "If you're working with months, multiply this figure by 12)
5) In A3, type "Amt Per Payment"
6) In A4, type "Arrears/Due"
7) In C4, type "(Key in 0 if the payment is made in the beginning of the period and 1 for the end of the period)"
8) In A6, type "Future Value = "
9) In B6, type "=FV(B1,B2,B3,0,B4)"

Suppose if you have to deposit $1000 at the end of every year for 5 years at an interest of 4%. To find the future value of this investment, do the following:

1) In B1, type 4%
2) In B2, type 5
3) In B3, type -1000
4) In B4, type 1

The future value of this investment will be worth $5,632 and the result will look something like this.

Try changing the values in B1, B2, B3 and B4 to see how the future value changes.

I hope that you found this blog useful.

Time Value of Money - Present Values

If you hate figures and are not very good with the calculator, then you've come to the right page. In this blog we'll learn how to calculate the present value of an amount without all those complicating formulas that you see in most finance textbooks. All you need to do is to open up a spreadsheet and you're ready. Just follow the step by step instructions on setting up the spreadsheet and you're on your way to working out the figures in a jif. Please note that the following instructions will only work if you follow the steps exactly as it says.

Present Value of a single payment

To calculate the present value of a single payment. Do the following:
1) In an excel spreadsheet , type "Interest Rate Per Year" in Cell A1.
2) In A2, type "No. of Years"
3) In A3, type "Value in the Future"
4) In A5, type "Present Value = "
5) In B5, type "=PV(B1,B2,0,B3)"

Suppose if you will be receiving $20,000 in a year from now and you would like to find out what this value is worth today if you know that the interest rate is 2%. To do this, you have to do the following:

1) Key in 2% in cell B1.
2) Key in 1 in cell B2 for the number of years.
3) In B3, key in -20,000 as the value in the future. This figure has be keyed in as negative for the answer to work.

The present value will be $19,607.84. This is what the $20,000 of the future is worth right now. The final result will look something like this:

Try experimenting a bit by changing the interest rate, amount or number of years and watch the present value figure change. Now wasn't that easy?

Present Value of a series of payments

To work out the present value of a series of payments, do the following:
1) In A1, type "Interest Rate Per Year"
2) In C1, type "(If you're working with months, divide this figure by 12)"
3) In C2, type "(If you're working with months, multiply this figure by 12)"
4) In A2, type "No of Years"
5) In A3, type "Amt Per Payment".
6) In A4, tyep "Arrears/Due"
7) In C4, type "(Key in 0 if payment is made beginning of the period or 1 for end of the period)"
8) In A6, type "Present Value = "
9) In B6, type "=PV(B1,B2,B3,0,B4)"

To illustrate an example, if you have to make a payment of $1000 at the end of each year for the next 5 years with an interest rate of 4% and you would like to know what will the total value of the figure be as at today. Do the following:

1) In B1, type 4%
2) In B2, type 5
3) In B3, type -1000
4) In B4, type 1 because payment is made at the end of the year and not the beginning of the year.

The present value that you will get is $4,629.90. The final answer will look something like this:

Try changing the interest rate or number of years or even the amount per payment to see how the present value changes.

I hope that you find this blog useful. In my next blog, I will cover future values.