NPV with a stepped rental

[Colmans]

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.

 

[Marcelo Branco]

Maybe with helper columns...

Something like this

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Value​

[/TD]
[TD]

Months​

[/TD]
[TD][/TD]
[TD]

Result​

[/TD]
[TD][/TD]
[TD]

Helper1​

[/TD]
[TD]

Helper2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

100​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD]

$4.012,29​

[/TD]
[TD][/TD]
[TD]

0​

[/TD]
[TD]

100​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

1000​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD]

100​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

8​

[/TD]
[TD]

100​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1000​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1000​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1000​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1000​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1000​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.

 

[Colmans]

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]

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]

Try the following:

[TABLE="class: grid, width: 200"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"]Pmt[/TD]
[TD="align: right"]#Months[/TD]
[TD="align: right"]%Rate/mo[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]$100.00[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]0.3000%[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]$1,000.00[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]0.3000%[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]NPV
[/TD]
[TD="align: right"]$23,483.44
[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

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]

Another way - without helper columns

Assumes the same discount rate (D2)


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Pmt​

[/td][td]

#Months​

[/td][td][/td][td]

Rate​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

100​

[/td][td]

12​

[/td][td][/td][td]

0,3000%​

[/td][td]

$23.483,44​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

1000​

[/td][td]

24​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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]

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"]Pmt[/TD]
[TD="align: right"]#Months[/TD]
[TD="align: right"]%Rate/mo[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]$100.00[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]0.3000%[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]$1,000.00[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]0.3000%[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]NPV[/TD]
[TD="align: right"]$23,483.44
[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[....]
=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]

Nevertheless, I prefer the original PV+PV formula.

Ok, i also try to avoid volatile functions.

But suppose you have this

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Pmt​

[/td][td]

#Months​

[/td][td][/td][td]

Rate​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

100​

[/td][td]

12​

[/td][td][/td][td]

0,3000%​

[/td][td]

$6.733,51​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

200​

[/td][td]

12​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

300​

[/td][td]

12​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

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

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Pmt​

[/td][td]

#Months​

[/td][td][/td][td]

Rate​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

100​

[/td][td]

12​

[/td][td][/td][td]

0,3000%​

[/td][td]

$10.959,95​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

200​

[/td][td]

12​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

300​

[/td][td]

12​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

400​

[/td][td]

12​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

No need to change formula

M.