Engineers-Excel.com

MultiFunction Plotter: Loan Repayments

Description| Fourier Series| Loan Repayments



Almost everyone comes across across mortgages or loans - whether it is for a house, a car, studies or financing a project. In this illustration, we will use the Multi-function plotter look at loan repayments. We will consider the most common type of loan, where a fixed monthly payment is made and the interest rate is constant.

Excel was primarily designed for accounting, and it has a comprehensive set of financial functions. The functions we will use are:

PPMT, which returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate; and

IPMT, which returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.

The Multifunction plotter is tweaked to make it easy to enter the data. The modified file can be downloaded here. Make the entries in the yellow cells.

The screenshot below shows the entries for a $500,000 loan at 10% over a period of 10 years.

Loan Payment

It is interesting to see how the curves become flatter with higher interest rates - the graph below shows the payments at 20% interest rate, the interest amount now drops at a much slower rate.

Loan Payment

Excel templates for loan calculations are available from the Microsoft Office Templates site.

Also See:

Technical Stock Analysis

Returns from non-periodic cash flows

Descriptive Statistics

Random Walk