8. An Amortization Schedule

We worked on part 3 of Financial Functions by beginning our work on an Amortization Schedule. The Amortization Schedule works in tandem with our Loan Payment Calculator and our Interest Rate Schedule.

The Amortization table shows us the Beginning and Ending Balances for each year of the life of a loan, as well as separating out what's been Paid on the Principle of the Loan, and on the Interest Paid. That's particularly useful if you should want to pre-pay a loan before its term expires. We worked with the financial funtion, PV, which yields the Present Value of a Loan. In our case, it calculates the Ending Balance for each year in the life of the loan when we provide the Loan Rate per Month, the number of Monthly Payments left (here we subtracted the actual Year from the Full term of the Loan), and (a negative) Monthly Payment Amount.

Because we copied the the PV, as well as the Principle and Interest Paid, down the column, we were able to see that when we changed the number of years of the life of the loan to 18, that the PV Function went negative in the 18th year and beyond. We were able to correct that problem by wrapping the IF Function around the Present Value, and using logic to express the PV as zero is it exceeded the number of years of the loan.

We had the IF Function check to see whether the year we were looking at was Less Than or Equal to the total number of Years of the loan. If so, the PV Function was executed. If not, a Zero was placed in the cell. We did something similar with the formula for Interest Paid - that is we had the IF Function check whether the Opening Balance for the Year was Greater than Zero.

And we finished up by making the Beginning Balance of the second year equal the Ending Balance of the first, and fill-handling down.

Materials Covered: MS Excel - Project 4:
Financial Functions, Data Tables, and Amortization Schedules, pp 294-302