Need Help to calculate cumulative Future Values

[jaideep211]

Hi All

I need a help with finding the correct formula for my excel model. I am trying to calculate cumulative future values of unequal cash flows as explained in the example below:

• In the screenshot above, in column D, I need to calculate the cumulative FV of the cash flows.
• I have cash flows coming in at different dates. These are not random dates but dates that increase in one direction.
• I can invest each of these cash flows at a certain interest rates.
• I need to be able to know what is the total FV of all cash flows at each given date.

• For D24, the answer is C24 + FV of all prior cash flows as at Nov 1, 2018 (B25)
• However, just copying the D24 formula will not help me obtain D25 since for D25, a new variable is added (refer to the formula in column E.
• In this example I have manually written the formulas for each cell in column D. However, I this method becomes impractical if I have nearly >300 rows to calculate.

I have considered creating an table such as this while copying the relevant formulas however, it again makes the excel too big.


Any help is much appreciated.

Thanks..!

 

[vletm]

jaideep211
As You already has an Excel-file, then You should upload it here

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

or
are You looking for some kind of snapshot-answer

 

[Peter Bartholomew]

This is no more than an implementation of your formula (I actually transposed your array because I wanted the result to be a column vector to fit into the table). Rather than using helper cells, I placed the array calculations into named formulas so they may be referenced and calculated on demand.


The MMULT function was then used to sum the rows


The result appear to tally with your example

 

[AliGW]

Cross-posted: https://www.excelforum.com/excel-ge...lp-to-calculate-cumulative-future-values.html

 

[vletm]

jaideep211
Please, ReRead:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

 

[Hans Knudsen]

Or maybe

=FV(C21,(B27-B23)/365,,-XNPV(C21,C23:C27,B23:B27))

 

[Peter Bartholomew]

Hello Hans
I suspect we are doomed to a cycle of inserting names and removing them again to understand each other's work!

Once I had managed to align your formula with my data, I obtained a final value, as at 29/12/2019, given by
= FV( Rate, (MAX(Date)-MIN(Date))/365, ,-XNPV(Rate, CashFlow, Date) )

To obtain the column of Future Values, I finished up with
= FV( Rate, (@Date-MIN(Date))/365, ,-XNPV(Rate, IF( Date<=@Date, CashFlow, 0), Date ) )
[ I recognise that this is somewhere the 'black art' of partial anchoring and dragging would pay off ]

Not having a finance background, I have not used XNPV before [for me, even T.INV.2T is more familiar], so it was interesting to find another full-blooded array function working away quietly with no CSE.

Note to others: Do not try this at home unless you are using an Office 365 insider version.

 

[Peter Bartholomew]

Just looked again. The value for the final date can be calculated from first principles using
= SUMPRODUCT( CashFlow, (1+Rate)^((MAX(Date)-Date)/365) )
I think this calculates correctly with or without DAs, with or without CSE.

 

[Hans Knudsen]

Hello Peter

Clever as always when you contribute!