Excel 2007 : How to Multiply the Maximum preceding variable by a specific percentage

Hi there,

I have a problem whereby I have a charge to apply to a the higher of two values.



I need to apply a 1.5% charge on an investment.

I need to apply that charge to the higher of either a) the initial investment e.g. €100,000. or b) the end of year fund investment value.

The balances are recorded at the end of every year.

Should the fund value go above the initial investment, that will become the new base figure to apply the 1.5% charge. Should the fund decrease in value and never recover then the 1.5% charge will still be based on the maximum end-of-year value in preceding years.

Initial Investment €100,000

Year Fund Value Charge

0 100,000 €1,500
1 110,000 €1,650
2 90,000 €1,650
3 85,000 €1,650
4 120,000 €1,800


I'd really appreciate any help on this, it has me pulling my hair out at this stage.

Thanks.

I should point out that the figures in the table at the bottom are merely there for reference purposes to show what the charges should be.

I have no idea how to apply a formula that traces the highest value of all the preceding years since inception of the investments.

Thanks.

I've attached the spreadsheet here.

Specifically it relates to column I (Guarantee Charge).

The charge for year 0 is applied as 1.5% of the €100,000.

This is the "baseline" charge that will be applied such that if the closing fund value in column J remains below €100,000 then the charge will be €1,500 every year from then onwards.

However, if the year-end fund value were to exceed the €100,000 then that would become the new baseline amount at which to apply the 1.5% charge (cell N13).

I would wish to find a formula such that as I go further down column J that it finds the highest value cell above it in the column and applies the 1.5% charge....but should such values be less than €100,000 then it will remain as €1,500

I hope I've made sense of that.

Many thanks.