Net Present Value calculations

[TheWkndr]

We are trying to use the NPV function to calculate a series of payments greater than 29 steps.

ex - We need to figure out the pv for 4 years of monthly payments where the first 24 are for $5,000 and the next 24 are for $6,000. The only way I can see to accomplish this is if we can specify that for the first set of payemnts, n = 1 - 24 and the next set of payments, n = 25 - 48. That way, we are starting in the future. Any ideas out there?

 

[MichaelRed]

Hmmmmmmmmmmmmmmmmmmmmmmmm ... mmmmmmmmmmmmm ...mmmmmmmmm ...


I'm thoroughly confused. NPV is (should not be) limited to any (pratical) number of steps. Where are you getting the procedure?

ONE way id to use the existing (Excel) function, which only requires a reference to the appropiate library. An alternative is to sinply use the help (or manual) from almost any spreadsheet app to obtain the process and code your own. I think I translated from a "PlanPerfect" manual over five years ago, and used it for a long time.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[cheerio]

Perhaps I should disclose that in addition to programming I am qualified as a UK Chartered Accountant.

NPV calculations can have any number of periods. However, the Excel NPV function has a built in limit of 29.

This is not a fundamental problem in that you can go back to first principles and do the calculation yourself.

Excel help has the following example:

Suppose you're considering an investment in which you pay $10,000 one year from today and receive an annual income of $3,000, $4,200, and $6,800 in the three years that follow. Assuming an annual discount rate of 10 percent, the net present value of this investment is:

NPV(10%, -10000, 3000, 4200, 6800) equals $1,188.44


This is the calculation from first principles:

0	     0	100%	
1	-10000	110%	 -9,090.91 
2	  3000	121%	 2,479.34 
3	  4200	133%	 3,155.52 
4	  6800	146%	 4,644.49 
			
			            1,188.44

Here 110% = 100%*110%; 121% = 110%*110%; 133%=121%*110%; 146%=133%*110%.

ie each row % is 110% times the row above. ie 100% plus the given interest rate of 10%.

Also here 9090.91=10000/110%; 2479.34= 3000/121%;3155.52= 4200/133% etc

Finally, 1188.44 is the sum of the values above.

Given this info you should now be able to calculate a NPV for any number of periods.

Ken