Net Present Value

[N Prakash]

Hi! Friends,

I am trying to calculate the NPV for a future value of Rs. 100/- compounded quarterly for various periods say @ 5%. I have used the NPV function and the result what I am getting is not correct. The calculated results should be as follows;

3 months 98.7654
6 months 97.5461
9 months 96.3418
120 months 60.8413

will you please help me with correct formulae.

Regards,
Prakash.

 

[RalphA]

Assuming that your compounding is every month, here's what I did:

In A1:A4, I entered 100
In B1:B4, I entered 5%
In C1:C4, I entered 3, 6, 9, 120
In D1, I entered =C1/3
In E1, I entered =A1*(1+B1*3/12)^D1

With ED1 highlighted, I clicked on Tools, Goal Seek, and entered in "To value:" 100, and in "By changing cell:", I entered A1. Then, I repeated these steps for each of E2:E4. The results I got are, for A1:A4:

98.7654
97.5461
96.3418
60.8413

which are identical to yours. Now, in order to calculate the present value, I solved the equation in E1, for the present value, A1, obtaining:

present value = (future value)/(1 + r%/4) ^ periods

For your case of 9 months, it would be:

present value = 100/(1+5%/4)^3, which gives:
present value = 96.3418, the same as Excel's Goal Seek gives.

Does this answer your question?




As a test, for the third case, 9 months, let's take the 89.4221, set it at 5% annually, compounded every 3 months

 

[Chitosunday]

Use pv function not npv
Months
3 =PV(0.05/4,3/3,,100)
6 =PV(0.05/4,6/3,,100)

 

[N Prakash]

Hi! Friends,

Thanks for the reply. Mr. Chitosunday formulae seems to be very simple and it has given the result.

Regards,
Prakash.