Calculating annualized ROI to date.

I have a spreadsheet where, in part, I am tracking annualized ROI percentage on investment. The part I'm (mainly) looking for potential feedback on is in bold.

Each row represents one month: "last_month_day_balance - month_day_1_balance" == ROI in dollars.

There's a column for ROI running total; and a column for ROI percentage: "( monthly_ROI ÷ month_day_1_balance ) * 12", to show the annualized rate.

To derive annualized ROI % to date: "( ROI_running_total_in_dollars ÷ starting_balance_for_the_entire_sheet ) * 12". (I use a dollar sign to preserve the row of the starting balance, so I can 'fill down' with correct results.)

In my testing, the calculations seem to be correct. I'm posting in case there might be a function, or something else that would be an improvement; or that this looks OK as is.

NOTE: I use a cel green background for data entered manually; light grey for calculated cel values; darker grey in select columns, row 1 only, to indicate the formula is different than rows 2 to the end, so I won't 'fill down' with them.

NOTE: I may later add a column to display annualized ROI %, where I reset the value each January so that I can also track annualized ROI % by year.

NOTE: To focus on getting the ROI data correct, I didn't mention earlier, but to derive (monthly) annualized ROI, I subtract day1Balance from lastDayBalance, but then add back in any withdrawals I made as well as any financial advisor fee (that isn't reflected above). (I could also have a column that shows ROI, factoring in the advisor fee.)

Posted on Feb 4, 2024 9:27 AM

Top-ranking reply

SGIII

Level 8

37,550 points

Posted on Feb 7, 2024 10:48 AM

Sounds as if you're getting there.

When contributions and withdrawals are involved then you need to calculate the Period Return a little differently.

Without them you did this,

took Ending as the numerator

took Beginning as the denominator

and subtracted 1

With them,

the numerator is Ending - Beginning - Contributions + Withdrawals.

the denominator is Beginning + Weighted Contributions - Weighted Withdrawals

The "weighted" (which occurs only in the denominator) accounts for when those occurred during the period. A contribution at the beginning of the period would be fully weighted by 1, at the end of the period weighted by 0, in the middle by 0.5, etc. Same with a withdrawal.

The idea in the numerator is to add back withdrawals and take out contributions. The idea in the denominator is to measure the "average" amount over which the adviser/manager had control during that period.

If the fee is debited from the account then it is already incorporated in the calculation because, like income and any other expenses, it is reflected in the ending market value. Only if it is billed separately to some other account do you need to subtract the fee from the Ending Value when calculating the period return.

To implement this you could consider adding columns after Ending Value (your Balance Last Day of Month) such as:

Contributions Withdrawals Wgt_Contr Wgt_Withdr Numerator Denominator

(you many not need all of these)

Derive the numerator and denominator, divide numerator by denominator to calculate the period return, and have those period returns flow through to your Pct Since Inception and Annualized Calculations.

View in context

16 replies

bob7 Author

Level 1

4 points

Feb 4, 2024 10:39 AM in response to bob7

This time showing both of the last two columns as Percentage:

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Calculating annualized ROI to date.