Course / Dates, Rounding & Money

Loan payments and savings growth

Use PMT to find a monthly loan payment, see what the loan really costs, and project a savings plan with FV. Intermediate · 5 min · PMT, FV, Total interest, Cash-flow signs

Two finance functions answer most everyday money questions:

• PMT(rate, nper, pv): the regular payment that pays off a loan
• FV(rate, nper, pmt): what regular deposits grow to

Both need the rate and the number of periods in the same unit. Rates are usually quoted per year, so for monthly payments divide the rate by 12 and multiply the years by 12.

> Excel's finance functions track which way the money moves. Money you receive is positive; money you pay out is negative.

Steps

1. What will the car cost each month? In B5, type =PMT(B3/12, B4*12, B2). The rate is per year, so divide it by 12; the term is in years, so multiply it by 12 to count the payments.

2. A negative payment reads oddly on a budget sheet. Edit B5 and put a minus sign in front of PMT so the payment shows as a positive amount.

3. What does the loan really cost? In B6, multiply the monthly payment by the number of payments. Then in B7, subtract the loan amount to get the total interest.

4. Now the savings goal. In E5, use =FV(E3/12, E4*12, -E2) to see what the monthly deposits grow to. The deposit is negative because it leaves your pocket; the balance you get back is positive.

5. In E6, total up what you deposited over the years. Then in E7, work out how much of the final balance is interest.

Remember

• PMT(rate/12, years*12, loan) gives a monthly payment, shown as negative because it is money going out.
• Use =-PMT(...) to show the payment as a positive number.
• Total interest = payment × number of payments − amount borrowed.
• FV(rate/12, years*12, -deposit) shows what regular saving grows to.

Previous: Rounding values (not just how they look) · Next: How Excel stores times