NPV with a stepped rental

Colmans

New Member
Joined
May 28, 2016
Messages
48
Hi

I'm trying to work out the NPV of a simple repayment profile that has a stepped rental i.e X months at $100 followed by Y months at $1000

I can do this by creating lists but I want to develop a calculator where I can solve the NPV by having X & Y as variable numbers. I have other tools I can use outside Excel to do this (TValue & HP17BII) but want to include this in a spreadsheet tool.

Thanks in advance for any help.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
Maybe with helper columns...

Something like this

A
B
C
D
E
F
G
1
Value​
Months​
Result​
Helper1​
Helper2​
2
100​
3​
$4.012,29​
0​
100​
3
1000​
5​
3​
100​
4
8​
100​
5
1000​
6
1000​
7
1000​
8
1000​
9
1000​
10

<tbody>
</tbody>


Helper1
Formula in F2 copied down
=IF(B1<>"",SUM(B$1:B1),"")

Helper2
Formula in G2 copied down
=IF(ROWS(G$2:G2)<=MAX(F:F),INDEX(A$2:A$100,MATCH(ROWS(G$2:G2)-1,F$2:F$100)),"")

At last, formula in D2 (using rate = 0.05)
=NPV(0.05,G2:G100)

Hope this helps

M.
 
Last edited:

Colmans

New Member
Joined
May 28, 2016
Messages
48
Marcelo, thanks for the reply

Is the code used supposed to populate the payment stream in G, id so this wasn't working. I only got a Zeros is G2 and D2
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
I'm not following you. What you mean by "is the code used supposed to populate the payment stream in G"?
Could you provide an example?

M.
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,737
Office Version
2010
Platform
Windows
Try the following:

ABC
1Pmt#Months%Rate/mo
2$100.00120.3000%
3$1,000.00240.3000%
4
5NPV
$23,483.44

<tbody>
</tbody>

The formula in B5 is:

=PV(C2,B2,-A2) + PV(C3,B3,-A3)/(1+C2)^B2

Note that column C has the monthly discount rate. Typically, that is annualRate/12. The model allows for different discount rates. Of course, you can simplify.

You can confirm the formula for the same discount rates by using the formula =NPV(C2,F1:F36), where F1:F12 are 100, and F13:F36 are 1000.

For different discount rates, confirm by using the formula =NPV(C2,F1:F12) + NPV(C3,F13:F36)/(1+C2)^B2, although that requires a "leap of faith" to some degree.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
Another way - without helper columns

Assumes the same discount rate (D2)


A
B
C
D
E
1
Pmt​
#Months​
Rate​
Result​
2
100​
12​
0,3000%​
$23.483,44​
3
1000​
24​
4

Array formula in E2
=NPV(D2,INDEX(A$2:A$3,N(IF(1,MATCH(ROW(A$1:INDEX($A:$A,SUM(B$2:B$3)))-1,SUBTOTAL(9,OFFSET(B$1,,,ROW(B$1:B$3)-ROW(B$1)+1)))))))
confirmed with Ctrl+Shift+Enter, not just Enter

M.
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,737
Office Version
2010
Platform
Windows
ABC
1Pmt#Months%Rate/mo
2$100.00120.3000%
3$1,000.00240.3000%
4
5NPV$23,483.44

<tbody>
</tbody>
[....]
=PV(C2,B2,-A2) + PV(C3,B3,-A3)/(1+C2)^B2
Or for a single discount rate in C2, array-enter (press ctrl+shift+Enter instead of just Enter) the following in B5:

=NPV(C2, IF(ROW(A1:INDEX(A:A,SUM(B2:B3),1))<=ROW(INDEX(A:A,B2,1)), A2, A3))

I prefer to use INDEX instead of OFFSET and INDIRECT because the latter are "volatile" functions, which cause the formula and all dependent formulas to be recalculated whenver any cell in the workbook is edited, as well as some other times that Excel chooses to recalculate.

Nevertheless, I prefer the original PV+PV formula.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
Nevertheless, I prefer the original PV+PV formula.
Ok, i also try to avoid volatile functions.

But suppose you have this

A
B
C
D
E
1
Pmt​
#Months​
Rate​
Result​
2
100​
12​
0,3000%​
$6.733,51​
3
200​
12​
4
300​
12​

Array formula in E2
=NPV(D2,INDEX(A$2:A$10,N(IF(1,MATCH(ROW(A$1:INDEX($A:$A,SUM(B$2:B$10)))-1,SUBTOTAL(9,OFFSET(B$1,,,ROW(B$1:B$10)-ROW(B$1)+1)))))))

Then you need to add another track

A
B
C
D
E
1
Pmt​
#Months​
Rate​
Result​
2
100​
12​
0,3000%​
$10.959,95​
3
200​
12​
4
300​
12​
5
400​
12​

No need to change formula ;)

M.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,914
Messages
5,471,473
Members
406,764
Latest member
ExcelMaker007

This Week's Hot Topics

Top