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.
No comments:
Post a Comment