Calculating annualized ROI to date.

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.

SG

Jerry,

Are you SURE you're giving good advice here on how to calculate investment returns?

"if you've been making 1 percent a month, and it looks as though you will continue at that rate, just estimate 12% a year and don't worry about the time value of money."

The SEC would like to have a word with you!

Seriously, do not annualize returns for periods under a year. And use the standard formula:

(Ending value/beginning value)^(1/n) -1

where n is the number of years

That already captures income. It has to be adjusted for any contributions/distributions, but it looks as if the OP has already done that.

SG

Just a heads up about annualizing rates of return.

Annualizing is helpful for investment periods longer than one year. The general formula is:

(Ending value/beginning value)^(1/n) -1

where n is the number of years

But for periods less than one year, annualizing is highly misleading. So misleading that it is prohibited in investment industry materials.

For the year, if you started with $100 and ended with $101, then your return should be about 1% (depending on any decimal places you are not showing).

Your schedule doesn't show whether you have any contributions (cash put in) or withdrawals (cash taken out). If there are any, then you need account for them in deriving the rate of return on the investment. That is a complicated subject, but post if you need further guidance on that.

SG

Not obsessive at all! More people should measure investment returns to check how advisers are really performing.

Here is a simple schedule illustrating how you can quite easily set up performance tracking if there are no contributions or withdrawals.

Columns A and C through E are values you enter. You also enter a 100 in G1.

In B2, filled down:

=A2/12

In F2 filled down:

=E2/D2−1

In G2 filled down:

=G1*(1+F2)

In H2 filled down:

=G2/G$1−1

In I13, filled down:

=(G13/G$1)^(1/B13)−1

Note the construction of an index in column G. It is essentially the same as "growth of $100" or similar that you see in some investment materials. Using an index can make calculations easier. For example if you wanted to calculate the return in year 2 you can simply use =G25/G13-1 (which is the same =(G25/G13)^(1/n)-1 where n is 1)

The -1 can seem confusing at first. But it's not complicated.

It is perhaps more intuitive to understand that percentage change is (Ending Value-Beginning Value)/Beginning Value. For spreadsheets that is more easily expressed as Ending Value/Beginning Value -1. It's just a simpler way to express the same thing.

If you do have contributions (or withdrawals) in some months then the schedule becomes a little more complicated. But the idea is the same. Construct an index and base return calculations on that. Post if you need guidance on how to do that.

SG

bob7 wrote:

I need to get the basics of 'annualized' ROI figured out.

Use:

(Ending value/beginning value)^(1/n) -1

where n is the number of years

But don't annualize if the period is less than one year.

SG

I think you are still incorrectly calculating your "ROI". At the end (12/24) you ended up making $1 on your $100 investment, which is 1%. For an investment with a single purchase and no sales (except at the end), ROI as a % =(current value - initial value)/initial value. This calculation isn't really all that useful as a tool to determine if it was a good investment (other than + is good and - is bad) because it does not take time into account. If you just want to know year over year gain, not the total gain, you can use (balance on this month - last year balance on same month)/last year balance on same month.

A better measure might be internal rate of return (the IRR or XIRR function). I am no wizard with the financial functions but here is an attempt to do something that works with what you have (without attempting to recreate all your columns):

D2 =EOMONTH($A2,0)

E2 =XIRR(UNION.RANGES(0,B$2:B2,C2),UNION.RANGES(0,A$2:A2,D2))

fill down with both to complete the columns

In column B, investments (purchases) are entered as negative amounts, sales (money out) is entered as positive amounts. Enter $0 when there is no transaction. You can have more than one of each. In Column A use the ACTUAL day of the month the transaction occurred.

XIRR in my example is calculating the annualized return of the investment to date, based on a 365 day year.

End of January: $100*(1+12.87%)^(30days/365days) = $101

End of February: $100*(1+13.03%)^(59days/365days) = $102

And so on, which shows that the percentages are the annualized rate of return. But it is better than the straight calculation because it can accommodate multiple purchases and sales made over the life of the investment.

Notice that the first row (January) is based on 30 days, not 31, because the investment was made on the 1st day of the month. At the end of the month it was owned for 30 days, not 31. This continues on, always one day short.

Looking good! On your questions ...

1- Yes, by all means use a formula to carry forward the ending balance of the previous month, so all you have to input is period number, the period "date", inception amount (D2) and ending balance for each month. Note that is all you need to derive the so-called "total return." That's because the effects of any income (and fees and expenses) are already captured in the ending balance.

2- You could copy the annualized return formula up into previous cells and it would work. But, as posted above, it's not a good thing to do. Annualizing results for periods less than a year can result in wild results, highly misleading, so misleading that is a no-no in the investment industry.

3 - You get that negative annualized return in your example for the first month of 2024 because, remember, you're calculating annualized return since inception (beginning of January 2023). The schedule correctly shows a positive return for that month, but a negative annualized return because of previous negative returns. Notice that the annualized return is less negative than the previous row, reflecting the positive return for that month.

Post if you do have any significant contributions or withdrawals, because those can have a significant impact and you will want to incorporate a way to adjust for those impacts.

SG

Bob,

I think you may be too far into the weeds, as they say. You have a series of gains that looks like it's headed for a 12% annual return and in the last month you take a hit that drops you to 1%. I don't see any problem with saying that if you've been making 1 percent a month, and it looks as though you will continue at that rate, just estimate 12% a year and don't worry about the time value of money. For a quick and easy guess at your projected return, use FV to estimate what you will have at the end of the year based on your present income projection. If you want ROI, you can start your FV with zero for the present value argument, and divide you FV result by your present value to get % return.

Jerry