Question:

Please advise how to calcuALTe the NPV and PV of an amount with interest rate say 6% and inflation rate of 2.5% over a 20 years period. In other words how to incorporate the inflation rate in the PV and NPV formulae.

Answer:

Assuming that you put $100 in the bank. And the bank pays you an interest of 6%. If you do not withdraw the amount ($106) at the end of 1 year, the bank will pay you interest of 6% on your $106, i.e. $112.36. If this continues for 20 years, you would have $320.7135472 in the bank. This is called the future value of $100 at the end of 20 years.

To factor in inflation, discount the amount ($320.7135472) by 2.5% (inflation rate). You will get $195.72. That is the present value of $320.7135472. This means that you could buy $195.72 of good and services (non perishable) today and keep them for 20 years, they will be worth $320.7135472 in 20 years time.

Deduct the present value ($195.72) from the amount you put in ($100) and you get $95.72. That is Net Present Value, the returns for putting $100 in the bank.

Question:

I have a loan that I have made for $500,000 and we have arranged for the customer to make 60 monthly payments of $9,666.40 at an interest rate of 6%. How do I determine the present value of these expected future cash flows?

Then part two of the question is what to do if the first 30 payments are $7,666.40 and the next 30 payments are $11,666.40?

Answer:

You can use the formula PV or NPV to determine the PV of the loan. PV formula allow to perform a quick calculation when the loan re-payment is constant. If not NPV is used (see scenario2)

You have to determine a discount rate for the loan. The discount rate is a measure of the risk you are taking with the loan. If it is a risky loan, the discount rate is higher and vice versa. This is usually not the same as the interest charged to the customer.

Nevertheless, if you use the discount rate of 6%, you will notice that the PV is lower for the one with variable payment. This is because the payment amount in the later period has a smaller PV. If you are to reverse the re-payment amount (e.g. scenario 3), you will get a higher PV than scenario 1 and 2.

Share this page:

Copyright © advanced-excel.com 2007 - 2019. All Rights Reserved. Privacy Policy

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.

advanced-excel.com is in no way associated with Microsoft