I am a relative neophyte in using indexed parameters. I want to calculate a cumulative cash flow by year from a table of cash flows, in order to calculate when a project will break even. I am looking for the syntax of a call on each row of my cash flow list to add it to the previous row of my cumulative cash flow list. Once I have that, I also want to calculate a similar list of discounted cumulative cash flows to understand the effect of discounting the cash flow. Is it worthwhile to create a second index with cash flow, cumulative cash flow, and discounted cash flow? Or is there an even more clever way to do this using features I am unaware of?
You are looking for the Cumulate function, which takes your cash flow by Year and returns the cumulated cash flow by Year. The syntax is:
Cumulate( Cash_flow, Year )
 To discount a cash flow, you just multiply Cash_flow by the discount factor for each year, like this:
Cash_flow * (1 - Discount_rate)^(@Year-1)
Here I've used @Year to number the years starting at 1. By using @Year-1, it means that the first year won't be discounted, whereas if you raise to the power of @Year you would discount the first year. Think about which convention you want. You can then pass this to the first parameter of Cumulate.
You want to compare the undiscounted case to the discounted case. A cool thing is that you can do this without duplicating all the logic for each case. The undiscounted case is simply the discounted case when the discount rate is 0. So, just make Discount_rate a Choice, i.e., between 0% and 7%, and then all your subsequent logic that uses discounted cash flow will work for either, or indeed for both at the same time (by selecting ALL for the choice), giving you the ability to compare any downstream variable without having to do anything additional.
In a short while, I will attach a model that demonstrates all of this and adds in the calculation for the breakeven year.
This looks great! And the tip on 0% discount is excellent. I look forward to your example, and hope to get back to this work this week.
This approach worked great. Â Now I can get to my reason for doing this. Â I would like to define a variable that finds the year when cumulative cash flow becomes positive, return the year, and add to that the value of cumulative cash flow for that year divided by the cash flow for that year, which gives me a number of years (with a fraction) to break even. Still looking to figure out how to do this.
Sorry about the delay with posting the model that I had promised. I hit a technical problem. Anyway, here it is:Â
It includes a variable that finds the breakeven year and a fraction of the breakeven year. I'm not sure if my fraction of the breakeven year is exactly what you just described, but hopefully you can tweak it if not.
I got taken away on other things (including a short vacation) and am just getting back to this reply. I had in the meantime discovered the Solar Panel model, which gets the breakeven year by using the CondMin function, but you have come at it a separate way. Either way, I am learning useful new functions! I will look at it and see if it gets the same answer I was coming to. Thanks!
