NPV without an outflow

Roelofg
Contributor

NPV without an outflow

Good morning

I am building a model consisting of future variable monthly cash outflows without any cash inflows.

 

I need to calculate the NPV of that in order to convert that into a liability using the variable cash outflows as down payments..

 

The NPV formula require cash in and cash out.

 

What are your suggestions?

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
ChrisHeathcote
Community Boss

Re: NPV without an outflow

@Roelofg 

It is possible to calculate the NPV as per your table. 

NPV can work with with data whose source is either dimensioned by time period or a transactions list. 

I will outline both approaches;

With Time

ChrisHeathcote_0-1624277514369.png

Cashflows are laid out using time dimension.

Without Time

ChrisHeathcote_2-1624278131717.png

Time dimension has been removed and a transaction list added. 

This is the result;

ChrisHeathcote_3-1624278168285.pngChrisHeathcote_4-1624278193714.png

The difference in values is due to the time calculation taking the full model calander and discounting over the full 3 years. The no time calculation only calculates for the values and dates presented and will stop discounting after the final transaction.

 

 

 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post

Misbah
Moderator

Re: NPV without an outflow

@Roelofg 

In Our April Group Event we discussed how NPV can be calculated without Anaplan timescale. Here is the link.

 

https://community.anaplan.com/t5/Modelers-Forum-India/Recording-of-Group-Event-13-04-2021/m-p/105085

 

Hope this helps

Misbah

View solution in original post

8 REPLIES 8
LipChean_Soh
Certified Master Anaplanner

Re: NPV without an outflow

Hi @Roelofg 

 

I would suggest that you come up with a mock up in excel, and share the screen shot here.

 

Thanks,

LipChean

Roelofg
Contributor

Re: NPV without an outflow

See below

Roelofg
Contributor

Re: NPV without an outflow

                 
                 
  5%              
                 
  Jan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21Jan-22Feb-22 
                 
Monthly 10001000100010001000100010001000100010001000100010001000 
Variable      500     500   
End of Contract              2000 
  10001000100010001000150010001000100010001000150010003000 
 NPV$16,422.30              
                 
                 
LipChean_Soh
Certified Master Anaplanner

Re: NPV without an outflow

Hi @Roelofg 

 

Have you given the NPV formula a try?

https://help.anaplan.com/c504a4f5-5513-42e3-91cf-b34d873d18f3-NPV

 

Thanks,

LipChean

Roelofg
Contributor

Re: NPV without an outflow

Hi LipChean_Soh

I used it but is concerned about the following statement:

 

 

NPV(d, c, [m, t]) where

 

  • c: Number: Cash flow. Must have at least one negative and one positive value

The cashflow does not have at least one negative. Does this have an impact on the answer?

 

Roelof

LipChean_Soh
Certified Master Anaplanner

Re: NPV without an outflow

Hi @Roelofg 

 

Looking back at your example, how did you arrive at 16,422.3

LipChean_Soh_2-1624260500018.png

 

When i tried to do a mockup in Excel, i arrived at 11,560.3

Please note that i moved 2000 to Feb21 to be added with the 1000 from Feb21.

 

Thanks,

LipChean

ChrisHeathcote
Community Boss

Re: NPV without an outflow

@Roelofg 

It is possible to calculate the NPV as per your table. 

NPV can work with with data whose source is either dimensioned by time period or a transactions list. 

I will outline both approaches;

With Time

ChrisHeathcote_0-1624277514369.png

Cashflows are laid out using time dimension.

Without Time

ChrisHeathcote_2-1624278131717.png

Time dimension has been removed and a transaction list added. 

This is the result;

ChrisHeathcote_3-1624278168285.pngChrisHeathcote_4-1624278193714.png

The difference in values is due to the time calculation taking the full model calander and discounting over the full 3 years. The no time calculation only calculates for the values and dates presented and will stop discounting after the final transaction.

 

 

 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post

Misbah
Moderator

Re: NPV without an outflow

@Roelofg 

In Our April Group Event we discussed how NPV can be calculated without Anaplan timescale. Here is the link.

 

https://community.anaplan.com/t5/Modelers-Forum-India/Recording-of-Group-Event-13-04-2021/m-p/105085

 

Hope this helps

Misbah

View solution in original post