Loan and Savings Formulas – PV, FV, and PMT

In a previous post I covered my amortization/depreciation template.

Here I will cover how to do present value and future value calculations that are used in that template. I will start with a savings example. Suppose I want to accumulate $500,000 in savings at the end of 25 years after making monthly payments. I will assume an average interest rate of 3%. My inputs will be as follows:

Present Value (current savings) = 0
Future value (target savings) = $500,000
Number of payments (n) = 300 (25 years * 12 monthly payments a year)
Interest Rate (i) = 0.25% (3%/12 months)

Payment  Calculation

To determine the size of the payment I need to be making to ensure I meet my target calculation, I will need to use the PMT (payment) formula. With the above inputs, my formula will look as follows:

=-PMT(0.0025,300,0,500000)

I enter a negative before the formula to ensure my value will be positive. This yields a result of $1,121.06. I could add an additional argument to say that the payments are at the beginning of the period as opposed to the end. All I need to do is add a another argument with the number 1, as shown below:

=-PMT(0.0025,300,0,500000,1)

The result would be $1,118.26; a difference of less than $3 a payment.

Future Value Calculation

I can test my calculations by now doing a future value calculation. My inputs remain the same, except now I have a payment amount.  The future value formula will look as follows:

=FV(0.0025,300,-1121.06,0)

I have made the payment amount negative so that the formula results in a positive number. My future value equals 500,001.53, confirming that I will reach the target amount with this payment amount. I could also change the payment number from 300 to 150, to determine how much I will have amassed halfway:

=FV(0.0025,150,-1121.06,0)

This tells me I will have a balance of $203,723.81 after 150 payments.

I’ll switch over to another example now. Let’s assume you have a mortgage and want to know what your balance is today. You can use a similar calculation, except this time you will have a negative present value and don’t know your future value (today’s value). Suppose a mortgage of $250,000, a 30 year mortgage with monthly payments (n=360), an interest rate of 5% and payments of $1,342.05 (this can be calculated in much the same way as the payment calculation was done for the first example).

My inputs are as follows (assume I want to know the balance halfway through the mortgage, after payment 180):

Present Value (mortgage amount): $250,000
Number of Payments (n) = 180 (15 years * 12 monthly payments a year)
Interest Rate (i) = 0.4167% (5%/12 months)
Payment = $1,342.05

The formula is as follows:

=FV(0.004167,180,1342.05,-250000)

Again you will notice the present value amount is negative here. This is because this is the amount owing. If this was the same sign as the payment amount the balance would increase rather than decrease. This calculation tells my the mortgage balance after 180 payments, or halfway through the mortgage would be $169,709.77. You may notice slightly different amounts because of the interest rate you use. In the above example I rounded to 0.004167 however if you reference the cell that has the interest rate calculation rather than a hard-coded number you will get a more accurate result.

Present Value Calculation

I will move on now to a present value calculation. In this example, I want to determine what mortgage amount can be afforded based on a specific monthly payment. Suppose I want the monthly payment to be $2,000; the term to again be 30 years; the interest rate to still be 5%. With these inputs I can determine what mortgage amount I can afford based on those assumptions. My formula will be as follows:

=PV(0.004167,360,-2000,0)

In the formula above I again set the payment amount to a negative so that the formula gives me a positive number. The result is a value of $372,563.23.

When doing these calculations you are always better off referencing formulas for interest rate calculation as opposed to hard-coded numbers. As you will notice, even a slight difference in the interest rate can have a big impact on your result, especially when dealing with a large number of payments. I have hard-coded examples in all of the examples here only for illustrative purposes but in practice I would recommend avoiding hard-coding an interest rate.