calculating the growth rate

[indiamacrotools]

Hi, we have a table: Sheet 1 (called as Main) Column C contains date from 1st January 1994 till 31st December 2008. Saturdays, Sundays and some holidays would be missing in the range. Column D contains NAV of say, Fidelity Magellan for the relevant dates in Column A. Now, we want to calculate the 1-year performance of Fidelilty Magellan Fund for each of the days in Column D in Sheet 2(called as 1yrret). Column C of Sheet 2 has the dates in the same chronological order as in Main (Sheet1) To elaborate in column D of Sheet2, we want to calculate the 1-year return for each of the days commencing from 1st January, 1995 till 31st December 2008 ie., if we have to calculate for each day of December 2008, excel should take the NAVs of the exact corresponding date of December 2007 and calculate.

We used vlookup and if functions, but could not get the right result for all the days. We used the following formula with no foolproof result: IF($C3732-365<>$C3494,(Main!U3732/VLOOKUP($C3732-365,Main!$C3447:Main!U3547,U$2-2,FALSE)-1)*100,IF($C3732-365=$C3494,(Main!U3732/VLOOKUP($C3732-365,Main!$C3427:U3652,U$2-2,FALSE)-1)*100,IF($C3732-366=$C3494,(Main!U3732/Main!U3494-1)*100,IF($C3732-367=$C3494,(Main!U3732/Main!U3494-1)*100,IF($C3732-368=$C3494,(Main!U3732/Main!U3494-1)*100,IF($C3732-369=$C3494,(Main!U3732/Main!U3494-1)*100,""))))))

Can you help us as to how to get accurate result.

Regards,
Prasad

 

[wsjackman]

note the initial if makes the

IF($C3732-365<>$C3494,(Main!U3732/VLOOKUP($C3732-365,Main!$C3447:Main!U3547,U$2-2,FALSE)-1)*100,

second if to not be needed since it is the "if false" of the first if

IF($C3732-365=$C3494,(Main!U3732/VLOOKUP($C3732-365,Main!$C3427:U3652,U$2-2,FALSE)-1)*100,

and the first if will stop any of the following from being done

IF($C3732-366=$C3494,(Main!U3732/Main!U3494-1)*100,IF($C3732-367=$C3494,(Main!U3732/Main!U3494-1)*100,IF($C3732-368=$C3494,(Main!U3732/Main!U3494-1)*100,IF($C3732-369=$C3494,(Main!U3732/Main!U3494-1)*100,""))))))

do you want < > or just > in the first if ? just > would make the rest of the formula useful.

the portion
IF($C3732-366=$C3494,(Main!U3732/Main!U3494-1)*100,IF($C3732-367=$C3494,(Main!U3732/Main!U3494-1)*100,IF($C3732-368=$C3494,(Main!U3732/Main!U3494-1)*100,IF($C3732-369=$C3494,(Main!U3732/Main!U3494-1)*100,""))))))

could be simplified to
IF($C3732-$C3494<370,(Main!U3732/Main!U3494-1)*100,"")))