1. Your first amortization schedule
In your last exercise, you built out the first 2 periods of an amortization schedule.
In this exercise, we'll be building out your first full amortization schedule, from the first period all the way down to the final period.
2. Remember the loan shark?
You have actually built a full schedule already in this course - in fact, it was one of the first things that you did, back when you borrowed $50 from your loan shark friend!
This schedule had all the important parts; it had the number of amortizing periods, the loan opening balance, and the rate - all highlighted in blue.
What made this a complete schedule was that the end balance was zero, as highlighted in green.
But, there were a lot of very deceptive elements on this schedule!
3. Removing deceptive elements!
In order to make sure that our loans are all comparable; so that we don't get deceived by our loan shark friend and his 5% interest rate, we will be adjusting some elements of our schedule to common, non-deceptive terms.
First, our interest rate will be stated in annual terms. This is actually a law in the USA, the Truth in Lending Act of 1968, which states that all loans must show interest in annual percentage rate, or APR, terms.
We'll be going over APR later in the course.
Second, the number of periods will be stated in years. This isn't a law, but it does make it easier to enter 15 year, bi-weekly payment loans later in the course.
Finally, the frequency of payment needs to be made clear; this is to correctly calculate the number of total payments as well as the interest rate per payment.
4. The more honest loan shark.
In this example, our friend the loan shark has decided to be more honest with us, and has stated the annual interest rate is 328.31%.
The payment period on this loan is daily. We won't be dealing with any more loan sharks in this course - so no more daily payment schedules either.
In this case, the loan is around 1.7% of a year. Once again, this is an uncommon schedule. We'll be dealing with full years going forward.
No more loan sharks!
5. Modifying formula for installments.
Since the interest rate and loan term are now in annual terms, the payment, interest and principal formulas need to be modified.
For the PMT() function, this means dividing the annual interest rate by the number of annual payments, and multiplying the number of years by the number of annual payments.
For the IPMT(), and the PPMT() formulas, you will need to do the same thing in order to prorate the interest rate and number of periods.
It's important to remember that the current period for the IPMT() and PPMT() formulas should not be in annual terms.
For example, the third monthly payment in the second year of a loan is period 15, not period 1.25.
6. Time to fix some amortization schedules!
Now, it's your turn to fix some amortization schedules to show in annual terms!