When calculating a down payment in excel, I noticed that by increasing my rate from 3.7% to 6.25%, the money I supposedly have to pay today decreases. Why is that?
Thanks a lot :)
1
closed as unclear what you're asking by NL - Apologize to Monica, Brythan, Dheer, JTP - Apologise to Monica♦ Jan 24 '17 at 2:22
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
-
5Can you give us some more information about what exactly you are trying to calculate in Excel, and what formulas/functions you are using? Maybe include a screenshot of your spreadsheet? – Ben Miller - Reinstate Monica Jan 23 '17 at 20:00
2
First, assuming you are making payments for a savings deposit. The present value of the deposit is the sum of the all the payments discounted to present value. In this case they would be discounted by the rate of inflation: £100 deposited next year is worth less than £100 today because it will be eroded by inflation. With a higher rate of inflation the payments are discounted more heavily, so the present value decreases. A deposit, or annuity due (see Calculating the Present Value of an Annuity Due), can be expressed mathematically like so:-
s = present value of the annuity
n = number of periods
d = periodic payment
r = periodic inflation rate
So for example, the following annuity has a present value of £1,136.76
n = 12 months
d = £100
r = 0.01 = 1% per month
s = (d (1 + r - (1 + r)^(1 - n))) / r = £1,136.76
The total amount that will be paid for the annuity is 12 x £100 = £1,200.
With a higher rate of inflation, say 2% per month, and with the same 12 x £100 payments, the present value of the annuity decreases.
r = 0.02 = 2% per month
s = (d (1 + r - (1 + r)^(1 - n))) / r = £1,078.68
In Excel
=PV(0.02,12,100,0,1)
(£1,078.68)
A similar case is that for a loan, or ordinary annuity (see Calculating the Present Value of an Ordinary Annuity), except the discounting factor is the loan interest rate rather than inflation and repayments are made at the end of each period rather than at the start.
The present value of a loan is the value of the all the future repayments discounted to present value. With a higher interest rate the payments are discounted more heavily, so the present value decreases. A loan can be expressed mathematically like so:-
s = present value of loan
n = number of periods
d = periodic payment
r = periodic interest rate
So for example, the following values fulfill a loan worth £1,125.51
n = 12 months
d = £100
r = 0.01 = 1% per month
s = (d - d (1 + r)^-n) / r = £1,125.51
The total amount that will be paid for the loan is 12 x £100 = £1,200.
With a higher rate of interest, say 2% per month, and the same 12 x £100 repayments, the present value of the loan that can be obtained decreases.
r = 0.02 = 2% per month
s = (d - d (1 + r)^-n) / r = £1,057.53
In Excel
=PV(0.02,12,100,0,0)
(£1,057.53)
answered Jan 23 '17 at 20:17
