See all Microsoft Excel tips
Strategy: Use the PMT function. Follow these steps.
1) Enter Price, Term in Months, and Annual Percentage Rate in cells B2:B4, as shown in Fig. 325.
The PMT function has three required arguments. The first argument is the interest rate. The second argument is the number of payments in the loan. The final argument is the original loan amount.
Gotcha: The interest rate must be entered as a percentage. If you are planning on monthly payments (which is normal), then you have to divide the annual percentage rate by twelve.
Gotcha: In financial terms, the bank is loaning you $25,000 – a positive amount coming to you. Thus, the payments that you make to the bank are really a negative amount – it is money leaving your wallet. For this reason, the result of the PMT function will be negative. However, you can precede the PMT function with a minus sign in order to return a positive payment amount.
2) Enter this formula in cell B6: =–PMT(B4/12,B3,B2). See Fig. 326.
Summary: The PMT function is great at calculating house or car loans.
Functions Discussed: =PMT()
See all Microsoft Excel tips