Formula related to a high watermark calculation

[badger6225]

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

[TABLE="width: 83"]
<tbody>[TR]
[TD="align: right"][TABLE="width: 83"]
<tbody>[TR]
[TD="align: right"]128.68[/TD]
[/TR]
[TR]
[TD="align: right"]142.68[/TD]
[/TR]
[TR]
[TD="align: right"]149.69[/TD]
[/TR]
[TR]
[TD="align: right"]173.32[/TD]
[/TR]
[TR]
[TD="align: right"]182.84[/TD]
[/TR]
[TR]
[TD="align: right"]115.19[/TD]
[/TR]
[TR]
[TD="align: right"]145.67[/TD]
[/TR]
[TR]
[TD="align: right"]167.60[/TD]
[/TR]
[TR]
[TD="align: right"]171.14[/TD]
[/TR]
[TR]
[TD="align: right"]198.52[/TD]
[/TR]
[TR]
[TD="align: right"]237.81[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
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.

 

[badger6225]

Sorry, I made a mistake in the description. Instead of saying "...take the value from the corresponding D cell if it is higher than the previous cell", I meant "...take the value from the corresponding D cell if it is higher than all the previous cells in the column"
.

 

[Aladin Akyurek]

[TABLE="width: 106"]
<COLGROUP><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2759" width=78><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl63, width: 78, bgcolor: transparent"]Old[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]New[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]128.68[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]128.68[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]142.68[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]142.68[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]149.69[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]149.69[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]173.32[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]173.32[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]182.84[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]182.84[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]115.19[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]182.84[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]145.67[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]182.84[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]167.6[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]182.84[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]171.14[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]182.84[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]198.52[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]198.52[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]237.81[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]237.81[/TD]
[/TR]
</TBODY>[/TABLE]

E5, copied down:

=IF($D5>N($E4),$D5,LOOKUP(9.99999999999999E+307,$E$4:E4))

 

[badger6225]

Thanks Aladin.

 

[Aladin Akyurek]

Thanks Aladin.

You are welcome.

 

[konew1]

I am sure that Aladin's solution will work, but MAX($D$2:D2) put in E2 and copied down will give the same result and be better understood by others maintaining the spread heet

 

[shg]

Or =MAX(E4,D5)

 

[badger6225]

Thanks everyone for the replies. Yes, the max function does simplify things, not sure why I didn't think of that before. I'm adding one more layer of complexity to this problem. The reason I am calculating the high water mark is to simulate the value of an investment portfolio when you include fees. However, the fee is only paid on an incremental gain above the previous watermark, in this case 10% of gains.

For example, let's say I start out with $1000 and I get 10% return in the market. The gain is $100, but I need to pay $10 in fees. So the new value of my portfolio is $1090. This is now the new watermark. Say the next year, I earn a positive return of 11.2125% and after fees the new value of my portfolio is $1200.

In year 3, the market drops 10%, so the new value is 1080.

What I'm trying to do in excel is say, any gains after year 3 should only be charged a fee once the high water mark of $1200 is breached. So in year 4, if I earn 10%, no fee is paid because 1080*1.1=1188 which is below the water mark. However if the return in year 4 was 20%, 1080 *1.2 = 1296, I should only pay a fee on 96 (1296 -1200), not the full 20% gain. In that case, I would pay $9.60 in fees and the new water mark value is $1296-$9.60 = $1286.40.

I've included the table of data below for an illustration. I'm stuck formulas for columns C and D.

[TABLE="width: 485"]
<colgroup><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Year[/TD]
[TD]Return[/TD]
[TD]Value[/TD]
[TD]Watermark (Value after fees)[/TD]
[TD]Fees[/TD]
[/TR]
[TR]
[TD]Yr 1[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]1100[/TD]
[TD="align: right"]1090[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Yr 2[/TD]
[TD="align: right"]11.21%[/TD]
[TD="align: right"]1212.22[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]12.12[/TD]
[/TR]
[TR]
[TD]Yr 3[/TD]
[TD="align: right"]-10%[/TD]
[TD="align: right"]1080[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Yr 4[/TD]
[TD="align: right"]20%[/TD]
[TD="align: right"]1296[/TD]
[TD="align: right"]1296[/TD]
[TD="align: right"]9.6[/TD]
[/TR]
</tbody>[/TABLE]

 

[konew1]

Ok, that makes it more interesting. Does this do it for you. Note I have a different formula in the first year, but on reflection I think they can all be the same

Excel Workbook
	A	B	C	D	E
1	date	Investment	Earnings %	Earnings $	Fee
2	1/01/2012	1,000.00	10%	100.00	10.00
3	1/01/2013	1,090.00	11.21%	122.22	12.22
4	1/01/2014	1,199.99	-10%	- 120.00	-
5	1/01/2015	1,080.00	20%	216.00	9.60
6	1/01/2016	1,286.39
Sheet9

 

[badger6225]

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