Need a formula to calculate cumulative capital gains for a portfolio of properties

[barnibald]

Greetings Excel Wizards

I have a waterfall setup (see screenshot 1) with the figures representing the values of properties in a portfolio. As you can see properties are acquired at different times. I need a formula to dynamically calculate the capital gains for each property, accounting for the fact that different properties are acquired at different times. The second screenshot is the outcome I need (although I have calculated this manually (and inefficiently!))

Edit: As requested I've uploaded a simple example worksheet (calculated manually)

Many thanks

[Pepe Le Mokko]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[barnibald]

Thanks Pepe I've done as you asked

[GeoffW283]

Please try in G5 copied down to G7:

Please Login or Register  to view this content.

This is the cumulative capital gain on each property.
In G8:

Please Login or Register  to view this content.

This is the total capital gain and is in agreement with your expected result in F19.

Let me know if this is what you were looking for.

[barnibald]

Hey Geoff

Thanks for the response. That is beautifully simple - I hadn't thought to use MIN. The only problem is if the assets depreciate (rather than appreciate) over the forecast period (which they don't in the simplified example I posted, but could in practice)

[barnibald]

Think I've got it ;

In the sample workbook I posted, if you use this formula in C14

Please Login or Register  to view this content.

and drag down/across, then take the sum, it should work,

[GeoffW283]

Another option that allows for depreciation. In G5 copied down to G7:

Please Login or Register  to view this content.

Then as before, in G8: =SUM(G5:G7)

Were you ideally looking for a single cell solution with the total cap-gain? Like the green highlighted cell F19?? If so, then that still eludes me, but if you're all set with what you have then I'm happy to call it a day