NPV Calculator

[juul2222]

Please be kind... i'm a rookie user. I'm trying to create a space planning and capital estimating tool that has several inputs. The one I'm struggling with is calculating NPV for a variety of scenarios. More specifically, I'd like a user to be able to enter a particular number of years on a potential space lease (round numbers from 1 to 15), Year 1 rent rate, annual rate increase as a percentage, and discount rate. I have the following formula for a 10 year lease, but it just seems like there is an easier way and then I don't want to have to build an IF function for all the different years. Perhaps I'm just lazy, but if there isn't an easier way, I'll get to coding.

=NPV(dr,usf*(1+lf)*br,usf*(1+lf)*(br*(1+ari)^1),usf*(1+lf)*(br*(1+ari)^2),usf*(1+lf)*(br*(1+ari)^3),usf*(1+lf)*(br*(1+ari)^4),usf*(1+lf)*(br*(1+ari)^5),usf*(1+lf)*(br*(1+ari)^6),usf*(1+lf)*(br*(1+ari)^7),usf*(1+lf)*(br*(1+ari)^8),usf*(1+lf)*(br*(1+ari)^9))

 

[StephenCrump]

If you want to do this calculation in a single cell, the NPV formula for level lease payments: usf*(1+lf)*br equates to

usf*(1+lf)*br * (1-(1+dr)^-N)/dr where N is the term.

You're inflating the lease payments at ari, so effectively you're only discounting at rate: (1+dr)/(1+ar)-1.
But inflation applies only from the second payment, so we need to adjust the result by a factor of 1/(1+ari)

So we can use the single cell formula:

usf*(1+lf)*br / (1+ari) * (1-((1+dr)/(1+ari))^-N)/((1+dr)/(1+ari)-1)

But probably the better way to illustrate what's going on would be for you to have a schedule showing lease payments from t=1 to t=15. You can then pass these as a range to the NPV function.

Or alternatively, you could also show the discount factors and use a =SUMPRODUCT(payments,discount factors) type formula.

 

[StephenCrump]

To better illustrate:

B11: =IF(A11<=N,Pmt*(1+ari)^(A11-1),"")
C11: =IF(A11<=N,(1+dr)^-A11,"")

B6: =Pmt / (1+ari) * (1-((1+dr)/(1+ari))^-N)/((1+dr)/(1+ari)-1) (Schedule not used)
B7: =NPV(dr,B11:B25)
B8: =SUMPRODUCT(B11:B25,C11:C25)

Excel 2010
	A	B	C
1	Term in years (N)	10
2	Initial lease payment (Pmt)	$1,000.00
3	Discount rate (dr)	5%
4	Inflation (ari)	3%
5
6	Result:	$8,747.60
7		$8,747.60
8		$8,747.60
9
10	Time	Payment	Discount
11	1	$1,000.00	0.95
12	2	$1,030.00	0.91
13	3	$1,060.90	0.86
14	4	$1,092.73	0.82
15	5	$1,125.51	0.78
16	6	$1,159.27	0.75
17	7	$1,194.05	0.71
18	8	$1,229.87	0.68
19	9	$1,266.77	0.64
20	10	$1,304.77	0.61
21	11
22	12
23	13
24	14
25	15
Sheet2

 

[juul2222]

Wow... impressive! I don’t necessarily need a 1 cell solution. In fact, a schedule would be pretty nice. I just don’t know how to do that with the necessary input fields and one scalable schedule. I can only think of doing a separate schedule for each possible situation (15 different schedules). That’s fine, but again, looking for simpler. If you have any thoughts on fractional years or even a dynamic rent increase input, I’m open ears!

 

[StephenCrump]

I'd build a single schedule myself, sophisticated enough to accommodate all the parameter variations you need to test, which may include payment frequency - monthly is probably a more realistic assumption than annually in arrears, but perhaps you also want to accommodate weekly or fortnightly?

Then you can store a range of scenarios, and generate an NPV, with supporting detailed schedule, for any given choice:

No: =B1
Scenarios: =E3:H6
B3:B6 =INDEX(Scenarios,,No) (array-entered over the whole range)

Excel 2010
	A	B	C	D	E	F	G	H
1	Scenario	2
2	Parameters to use in Schedule:			Stored Scenarios
3	Term in years (N)	10		Term in years (N)	5	10	.	10
4	Initial lease payment (Pmt)	$1,000.00		Initial lease payment (Pmt)	$1,500.00	$1,000.00
5	Discount rate (dr)	5%		Discount rate (dr)	5%	5%
6	Inflation (ari)	3%		Inflation (ari)	2%	3%
7
8	Calculated NPV (from detailed schedule)	$8,747.60	(say)
Sheet1

 

[juul2222]

I'm having trouble with the formulas that contain ari, dr, etc. This is going to expose the fact that someone else provided me with the initial formula I posted as well as showcase how much help I need, but how do those work if it's not referencing a particular cell?

B11: =IF(A11<=N,Pmt*(1+ari)^(A11-1),"")
C11: =IF(A11<=N,(1+dr)^-A11,"")

B6: =Pmt / (1+ari) * (1-((1+dr)/(1+ari))^-N)/((1+dr)/(1+ari)-1) (Schedule not used)
B7: =NPV(dr,B11:B25)
B8: =SUMPRODUCT(B11:B25,C11:C25)

 

[xenou]

Possibly those are named cells, otherwise I don't think they *would* work.

 

[StephenCrump]

I'm having trouble with the formulas that contain ari, dr, etc.

Google "Named ranges in Excel" and you should find some helpful articles, video clips etc, e.g. such as this one: http://www.contextures.com/xlNames01.html

Here's my workbook in case you find it a useful starting point: https://app.box.com/s/cjtinw20ryx71lesx8vqhtg8zvvnkz1q

Please post back if you need further help.

 

[juul2222]

Your help is much appreciated... I can’t thank you enough!