I need to use PV instead of NPV in Excel. However I am not able to understand why it gives different values

Question

I have a case where in I need 17,00,000 (end of 1st year) increased by 6% every year for 24 more years (so total 25 years). When I discount it using NPV with 10% rate, I get 2,56,64,794. However for this I have to use many cells for 17,00,000 compounding for 24 years and then discount with NPV for 25 years.

However I need to get the calculation done with PV. So for this I get real rate of return i.e.(10%-6%)/(1+6%) However when I use the PV formula with this rate and PMT as 17,00,000 (end of period payments), I get the value 2,72,04,681

Why do I get 2 different values? What am I missing here?

I need to get PV (as i need to lessen the calculations required) same as the NPV but cant wrap my head around the difference in results.

Why do you have `PMT as 17,00,000`? From you screenshot, it looks like `PMT = 0` — Ron Rosenfeld, May 24 '19 at 10:22
If `PMT` is `17,00,000` that means you are depositing that amount every year. Your screenshot does not reflect that. — Ron Rosenfeld, May 24 '19 at 18:03
OK, now I see that your screenshot is showing only the annual deposit, not the balance. Since your PMT varies each year, PV is not appropriate as it calls for a fixed payment. — Ron Rosenfeld, May 24 '19 at 18:13
@RonRosenfeld right.... i got that later on. But to my surprise i got the thing to work with PV in a single cell without use of NPV with small tweaks. Posted the answer just now. — 7bluephoenix, May 24 '19 at 18:21

7bluephoenix · Accepted Answer · 2019-05-24T18:19:46.177

Thanks to @cbcalvin, got the answer by breaking down the cash flows year by year.

PV is used for fixed cash flows in fututre whereas my requirement was for increasing PMT value.

Use of NPV is wider, however in my case it gave a steadily increasing PMTs.

For beginning of Year 1 values for both PV and NPV come to same when subtracted 17,00,000. Thus I get the following result in a single cell (marked in yellow) rather than calculating for every year and then discounting using NPV.

My case was for end of period. For start of period it works straight forward without any changes to either formulas just by taking right interest rates.

cbcalvin · Answer 2 · 2019-05-23T21:11:48.797

-1

PV = Today's value of some future amount.

PV=FV/(1+i)^t

NPV=Today’s value of the expected cash flows minus Today’s value of invested cash NPV is the sum of a stream of cash flows in future periods discounted from each future period to today. NPV=SUM(R1/(1+i)^1,R2/(1+i)^2,...,Rt/(1+i)^t)

For the stream, NPV is appropriate. For a known single future amount, PV.

edited May 23 '19 at 21:11

answered May 23 '19 at 21:04

cbcalvin

25
1
4

In excel, PV is used as a financial function, which calculates the Present Value of a future sum of money or **fixed cash flows** at a constant rate of interest. You can check https://www.wallstreetmojo.com/pv-function-in-excel/ for more info. – 7bluephoenix May 23 '19 at 21:30

I need to use PV instead of NPV in Excel. However I am not able to understand why it gives different values

2 Answers2