How to Better the below formula without much Manual intervention?

prveen

New member
Joined
Aug 20, 2015
Messages
4
Reaction score
0
Points
0
Hello,

I have excel sheet which every month in Calculation Sheet I had new row (Below Row-59) and adjust the formula in Historicals to match for 1Month,3months,1Yrs,3 Yrs and so on.

My Questions is there a way to better the formula to i just need to add the data in "Calculation Sheet" and sheet picks up the data accordingly with out any manual intervention.

I have attached my excel sheet example.

Thank you
 

Attachments

  • Calc_example.xls
    68.5 KB · Views: 19
This solution has been developed in Excel 2003 since you supplied a pre-Excel 2007 .xls file. It made it a bit more difficult than later versions of Excel.
First you should cearly separate that data in B4:G50 on the Calculation Sheet from the cells below which have some calculations in. Insert some blank rows, say 3 rows.
Then you need to convert the data in cells B4:G50 on the Calculation Sheet to a List (a Table in more recent versions of Excel):Select B4:G50, then dropdown menus Data|List|Create List, click OK (headers should be included).
The reason for this is that Excel automatically adjusts the size of the List to accommodate addition and removal of rows.
To add a new row of data, you click on that bottommost row of the List and a new blank row will appear below, click in that and start entering/pasting your new data.

In the attached, on the Historicals sheet, you'll find some formulae, for example in cell D4 you have:
=FVSCHEDULE(1,TrackingDiffYTD)-1
which replaces the original formula:
=FVSCHEDULE(1,'Calculation Sheet'!F45:F50)-1
Both formulae refer to the same cells and give the same results.
Likewise with cell D9 which now contains:
=(((FVSCHEDULE(1,PortfolioYTD)^(12/7)-1))-((FVSCHEDULE(1,OneMoRiskFreeYTD))^(12/7)-1))/(STDEV(PortfolioYTD)*SQRT(12))
replacing:
=(((FVSCHEDULE(1,'Calculation Sheet'!D45 : D50)^(12/7)-1))-((FVSCHEDULE(1,'Calculation Sheet'!G45:G50))^(12/7)-1))/(STDEV('Calculation Sheet'!D45: D50)*SQRT(12))

I've added some dynamic named ranges to the workbook, and although the names I've given these ranges are longish, it should be fairly obvious what they refer to:

BenchmarkLast12Rows
BenchmarkLast3Rows
BenchmarkYTD

OneMoRiskFreeLast12Rows
OneMoRiskFreeLast3Rows
OneMoRiskFreeYTD

PortFolioLast12Rows
PortfolioLast3Rows
PortfolioYTD

TrackingDiffLast12Rows
TrackingDiffLast3Rows
TrackingDiffYTD

I've not altered any formulae on the Calculation Sheet, and they should look after themselves.
On the Historicals sheet, I altered cells C4:E5 and C9:E11 to include these new names.

Now, the only cells which give the same resuts as your original table are in the YTD column! That's because I suspect your formulae for the last 3 months and the last 12 months might not refer to the correct ranges, especially C9, the Sharpe ratio for the last 3 months, seems to inlude far too many cells. Other formulae for the last three months only seen to include 2 month's data, and formulae for the last year seem to include only 11 month's data. If I'm wrong, it's easy enough to change just 1 or 2 names (not one of the ones above) on which other names rely.
The YTD named ranges will adjust automatically to the last year in the source data as new data moves from one year to the next. (I'm assuming YTD means always data from the 1st Jan of the last year?)
 

Attachments

  • ExcelGuru4827Calc_example.xls
    77 KB · Views: 14
Last edited:
I wonder if prveen even saw this response about 3 weeks ago?
 
I wonder if prveen even saw this response about 3 weeks ago?
Many times have I tried, worked and lost my time, and at the end, initiator question is never answered.
This is the price we pay if we want to help
Deal with it ;)
 
Back
Top