Financial Calculation Software, a Snap with MS Excel - Calculating the payment on a loan

(Page 2 of 4 )

We will be looking at fixed interest loans which are normally quoted for 30, 15, and 10 years. This type of loan requires you to pay back equal installments over the period of the loan. In the early part of your loan you will be paying more interest and less principal, and towards the end of the loan period more principal and less interest. There is a plethora of financial loan products apart from this kind of loan. What is assumed in the fixed interest loan is that the rate does not vary during the life of the loan.

In this tutorial we will be asking the question, suppose one borrows $10,000.00 at an interest of 8% per annum (per year), to be paid back in 10 months -- what will the monthly payment be? Since banks and loan companies want you to pay on a fixed date each month, you may also want to know the effect of paying at the beginning of the month the loan is due, or at the end of the month the loan is due.

The MS Excel function PMT

The Excel function PMT calculates the periodic payments that one needs to pay in order to liquidate the loan over a period of time. The syntax of this function is as follows:

PMT (rate, nper, pv, fv, type)

This function has five arguments. The first three are mandatory and the last two have default values, if left out. In the definition, the argument rate refers to the rate applicable to a single repayment period. Let us say the annual interest is 8% and the loan is to be paid off in 10 months. This requires us to set rate applicable to monthly rate, or 8%/12. The argument nper is the number of equal payments agreed upon, and the argument pv is the amount that is borrowed. Since the loan is going to be liquidated, the future value, argument fv is 0. If fv is not used in the function then it is assumed to be 0. The argument type has two values, 0 or 1. If the payment is made at the end of loan period, it is zero; if the payment is made at the beginning of the pay period, it is one. The default value for Type is 0.

Next: Usage of function PMT >>
 

More Microsoft Access Articles
More By Jayaram Krishnaswamy

Comments On: Financial Calculation Software, a Snap with MS Excel

· The financial functions in Excel are marvelous, however they require you and your...    · As the saying goes you can do things in several ways. In fact it is good to know...   >>> Post your comment now!