VLOOKUP Issue with Dates

[Dome.LM]

Hello, I'm having issues with a VLOOKUP formula that tries to fetch the last and first month of a range to calculate annualised return.

I think the issue relies on the date formatting but until now I've failed to fix it, I've tried the text to columns solution, adding the day as well as of course formatting the cells.

I've highlighted in yellow the formula that I'm trying to fix, disregard the ones in red as these will get fixed if I find a solution for the yellow cell.

Since it's too big for the upload option I've included a gdrive link, I hope it's ok: LINK

Thanks,

Dom

 

[Marc L]

Hello, if it's for Google Sheets you may be not on the right forum !​

For Excel : attach a well representative sample workbook and check before if the dates are not text at least, if data well sorted, …​

​

 

[p45cal]

I offer a solution that involves working out the annualised return from the raw data, without the intermediate helper tables that you use.
Have a look at the Analysis tab in the attached file, and compare your value in cell D9 with the value in cell D13, likewise E9 & E13.
The calculation method, step by step is outlined in cells I9:T9 where:
I9 is the sorted and filtered dates and NAV values according to the start and end dates.
L9 is the first of those values
O9 is the last of those values
R9 is the fractional years between those actual dates (not necessarily the actual dates in Start_Date and End_Date)
S9 is the total return
T9 (yellow) is the annualised return

If you agree that this method is OK, then this is what has been used in the AnnualisedReturn lambda function used in the other cells (V9, D13, E13).
There are hints for what's needed for the arguments:


where Source is the 2 adjacent columns Date and NAV in that order.

There is no need for the formulae in I9:V9 (there just for info).
Cell F13 contains a result dependent on the choice made in cell C4 (Fund_3)