Question: I’m going to plan a holiday, and I’d like to save $10,000 to cover all expenses. How do I figure out how much I need to pay into my savings each month to get to my target?
Answer: The PMT function can help
Process (Excel 2003 and 2007):
When it comes to finance, personal or business, knowing what you’re paying or how much you’re spending is paramount. Knowing how you can save money by adjusting your payments is a very handy thing, and we can use the PMT function to not only work this out, but see what we would have to pay before we enter into a loan agreement.
The PMT function has the following syntax:
=PMT(rate,nper,pv,[fv],[type])
Rate: this is the interest rate to be paid per period
Nper: this is the number of periods in the life of the loan (i.e. the number of payments to be made)
Pv: the present value of the loan amount
[Fv]: the future value (optional)
[Type]: shows whether payment is to be made on the first day or the last day of the month
In this example, we’re going planning to go on an overseas holiday in three years time. In order to do this, we’ll need to save $10,000 to cover costs of flights, accommodation etc. So how much will I need to pay into my savings account, at a set interest rate, each month for three years to get to my target?
1. Select cell B10, type in =C5/12 and press Enter
In order to work out the amount per period, we need to divide the yearly interest rate (12%) by the number of periods in the year. In this case, we’re making deposits on a monthly basis, hence dividing the rate by 12.
2. Select cell C10, type in =C4*12 and press Enter.
The savings plan is over 3 years, with payments made monthly. Therefore we need to work out the total number of periods, hence multiplying the loan years by 12.
3. Select cell D10, type in 0 and press Enter.
There isn’t a present value amount – we have no savings to start with. Hence, PV = 0.
4. Select cell E10, type =pmt(B9,C9,D9,C3) and press Enter
The formula references the interest rate for the period (rate = B10), the number of periods (nper = C9) and the present value of the loan (pv = 0). Since we’re trying to determine payments against a future amount, we can now use the [fv] option – choose the future value we’d like to work to (in this case, cell C3)
The amount is represented as a negative figure because this is money that you will be paying out. If you would like to see it as a positive number, simply multiply the result by -1. However, what if we actually began our savings plan with $2,500 in the bank?
5. Select cell C6 (the currently saved field), type in -2500 and press Enter
The currently saved amount needs to be entered as a negative: this money is being paid out by you into your savings fund, so it’s a negative amount.
6. Select cell D9, type in =C6 and press Enter.
By including the currently saved amount (i.e. the present value), you have now added extra money to your plan, and significantly affected your repayments for the better.
