Formula related to a high watermark calculation

[badger6225]

Hello, I have the following column of data in D5 through D15.

128.68 142.68 149.69 173.32 182.84 115.19 145.67 167.60 171.14 198.52 237.81 <tbody> </tbody>

<tbody>
</tbody>

What I'd like to do in E5 through E15 is take the value from the corresponding D cell if it is higher than the previous cell. For example, cell E5 would take the value of D5 (128.68). E6 would take D6 (142.68). This would continue through E9. However, E10 would not use the value of D10, it would have the same value as D9 because D10 is lower than the previous high in column D.

The value for E11, E12 etc should continue to use the old high until it has been broken. For example, E11, E12 and E13 will still use the value from D9 because it is the most recent high. E14 will have the new value from D14 (198.52) because it is the new high in column D.

Any help is appreciated.

 

[konew1]

Thanks for the comment. Looking at the formula again I see it has a redundant MAX function and surplus brackets. this is a tidier version.
=MAX(0,(B3+D3-MAX($B$2:B3))*0.1)
Cheers

 

[RyanSc]

Konew, thank you very much, this was very helpful.

Interesting thread - been a while since anyone posted on it however I have an additional layer of calculation that I would be grateful for guidance on.

Let’s say that the account holder frequently deposited and withdrew funds from his investment account.
Therefore, the HWM would need to strip out these transactions as they are not profits of losses.

This is a basic concept yet I have had no end of difficulty attempting to remedy the equation.
I can be more specific and upload a workbook if necessary.

NB: I've attempted using 'an adjusted start balance' and have also attempted ignoring (for performance fee considerations) the HWM, but have rather opted for cumulative new profits; neither of which have I been successfully able to implement.
Pleeeaaaaassssseee help,
Many thanks,
Ryan

 

[konew1]

I think you need post a section of your spreadsheet (remove any confidential info)
While it is easy enough to take the opening balance and adjust for additional money placed on investment (or withdrawn) you run into a problem with calculating the HWM

You will also need to allow for the timing of the additional deposits/withdrawals.
A lot more info is required to make a suggestion,
If you are just checking what some investment fund manager is doing, then you need to apply all the steps that are in the investment agreement

 

[RyanSc]

Hi Konew1
Many thanks for your reply.
Agreed re HWM problems.
I've tried looking at it from a 'New Profits HWM' perspective as well with inconclusive results.
Re timing, I am making the assumption that the withdrawals were executed after (market) hours on the last day of the month.
I've also enabled an option to strip out the withdrawals and separately the performance fee for error checking purposes.
The workbook has been uploaded here:

https://www.dropbox.com/s/5sx8ts4l8yazp3u/MrExcel-Investment-Performance-Workbook.xlsx?dl=0

Many thanks in advance for your consideration with this groundhog excelhell.

Ryan