CODEWITHSUNDEEP
excelfinance
Serguei
Serguei

Reputation: 33

FV and PV formula's in Excel

I'm trying to calculate a lifetime value of a customer. Let's assume a new customer pays $100K per year and stays for 5 years. Let's discount any future years' payments with 10% rate.

This is manual calculation:


    Year 1  $100,000.00
    Year 2  $90,000.00
    Year 3  $81,000.00
    Year 4  $72,900.00
    Year 5  $65,610.00
    ---------------------
    Total   $409,510.00

I can get the same value by using FV with negative rate.


    FV(-0.1,5,-100000,0,0) = $409,510.00

What I'm trying to do is to get the same value using PV. And it's not exactly the same:


    PV(0.1,5,-100000,0,1) = $416,986.54

I'm not sure what am I missing here. Does MS Office Excel 2010 PV understand discounting differently?

Upvotes: 3

Views: 618

Answers (2)

Grade 'Eh' Bacon
Grade 'Eh' Bacon

Reputation: 3823

If you calculate out what PV is doing manually, the formula is actually this, for each individual year:

=Base Amount / (1 + Discount Rate) ^ Periods

Vs what FV is doing manually, the formula is this (which you seem to know based on coming to the same answer in your data):

=Base Amount * (1 - Discount Rate) ^ Periods

The reason for the difference in calculation is the mathematical difference between the two items - for background see here: http://www.investopedia.com/walkthrough/corporate-finance/3/time-value-money/future-value.aspx and here: http://www.investopedia.com/walkthrough/corporate-finance/3/time-value-money/present-value-discounting.aspx.

In short, if you have $100k today, and invest it in something which gives you 10% each year, then each year you add 10% of the current balance to get the new balance. ie: in year 1 you add 100k * 10% = 10k, giving a new total of 110k; in year 2 you add 110k * 10% = 11k, giving a new total of 121k, etc. - Mathematically, each year's amount is given by the formula listed above for the FV calculation.

Where this gets tricky is that you are giving yourself a negative interest rate - meaning every year, the value is decreasing each year by 10%. You have attempted to use the FV calculation with a negative interest rate, but that's not quite correct. What you should be using is the PV formula.

For the PV formula, if you know that you will receive 100k each year, you need to determine how much cash you would have needed originally, in order to earn the same amount - that is the present value of the cash flow stream. Now, you need to 'gross-up' the value of each year's income stream. The formula for this gross-up is derived mathematically and results in what I have above there for PV. Think about it like this - if there's a shirt that normally costs $100 and is now 30% off, you can see that you simply multiply it by 30%, to get $70. But if you see of shirt on sale for $70, and it's 30% off, then to determine the original base price you need to take $70 & divide by .3 - which gives us $100.

To prove to yourself that the PV formula is appropriate, take the income stream of, say, year 4 [3 periods of interest later, assuming first payment is received in day 0]: 100k / (1 + 10%)^3 = $75,131. Now, work backwards - if you want to know the future value of a $75k investment held for 3 periods of interest compounded annually with a 10% annual rate, you go: 75,131 * (1 + 10%) ^ 3 = 100k.

This is an important financial distinction, and you should read over the sources I've linked to ensure you understand it.

Upvotes: 4

ShakesBeer
ShakesBeer

Reputation: 293

There is a difference in the calculation. FV takes 100,000 and discounts it by 10% to the number X so that X is 90% of the original value (i.e. X=90,000). PV by contrast discounts it to the number X such that 100,000 is 10% more than X. Quick math says X will be 10/11 of 100,000, i.e. 90909.09.

Indeed, if we apply this calculation 5 times:


    Year 1  $100,000.00
    Year 2  $90,909.09
    Year 3  $82,644.63
    Year 4  $75,131.48
    Year 5  $68,301.35
    ---------------------
    Total   $416,986.5

I don't know if there is a way to make them behave the same way (I don't think there is, as they're calculating different things), but since FV solves your problem why not just use that?

Upvotes: 2

Related Questions