I have found templates to do an amortization, but I am not number savvy enough to figure out how to change the interest rate. My specific need is to change the interest rate 11 times in 17 months. Any answers to my dilemma would be greatly appreciated. This is the template I am currently trying to adjust http://www.iworkcommunity.com/content/2948
MacBook Pro, OS X Mavericks (10.9.5)
I am not very sure how all this works, but I think that you would need to add two new column; One with the interest rate, and the other with the number twelve.
This way when the interest rate changes you can then change your periodic rate when you need to. You can hide these columns like you hid the rows.
BC,
If you are content with compounding once per month, and your payments are one per month, it's a simple matter. You don't need the template.
The main difference between the usual case where the rate is fixed, is that you will need to have a column for rate, rather than taking it from a separate parameter table. If you do this, you can change the rate every month and all will be well. The burden in that case is that you will need to enter the rate every month, a simple matter of dragging the Fill handle to copy the last month's rate if it didn't change.
This is the referenced template, first month.
Payment Due Date | Payment Number | Beginning Principal | Payment made | Extra payment | Extra Payment Date | Interest Payment | Total Interest | Principal Payment | Remaining Principal |
Jan 25, 2009, | 0 | $7,000.00 | $0.00 | $100.00 | Jan 27, 2009 | $0.00 | $0.00 | $100.00 | $6,900.00 |
You can modify it for your needs. Here's an approach that might be acceptable to you:
If you examine it, you will see that the interest rate changes several times, as does the actual amount paid in a given period, yet the loan balance goes to zero as of the last payment.
The programming is as follows...
C3: =F1
J3: =D1
A4: =EDATE($B$1,ROW()-3)
C4: =C3-H3
D4:=-PMT(B4/12,$D$1-ROW()+4,C4,0)
F4: =B4/12*C4
G4: =SUM(F$4:F4)
H4: =E4-F4
I4: =C4-H4
J4: =$J$3-ROW()+3
After pasting those formulas into Row 4, Fill Down as far as you need, the last row being number of periods in the loan plus 3.
Regards,
Jerry
Thanks Jerry! This is what I was looking for I appreciate the help and the functions.
-Brooke
Brooke,
You're welcome.
If you have any difficulty, post back. I tested pretty thoroughly before posting, so you should be fine. One point, in case it's not obvious, is that the first three rows are Header Rows. The reason for row three, the hidden row, is so that all the body rows can be identical in terms of formulas. That way you can simply use the Fill tool to extend the table for longer loans and the formulas will copy down.
Jerry
How do I make an amortization table with a changing interest rate?
