How can I calculate annualized rate of return on investments in numbers

Bruce,

Numbers has an IRR function. I think it should do what you want by default.

Jerry

From the Function Browser description:

The IRR function returns the internal rate of return for an investment that is based on a series of potentially irregular cash flows (payments that do not need to be a constant amount) that occur at regular time intervals.

IRR(flows-range, estimate)

flows-range: A collection that contains the cash flow values. flows-range must contain number values. Income (a cash inflow) is specified as a positive number, and an expenditure (a cash outflow) is specified as a negative number. There must be at least one positive and one negative value included within the collection. Cash flows must be specified in chronological order and equally spaced in time (for example, each month). If a period does not have a cash flow, use 0 for that period.

estimate: An optional number value specifying the initial estimate for the rate of return. estimate is entered as a decimal (for example, 0.08) or with a percent sign (for example, 8%). If estimate is omitted, 10% is assumed. If the default value does not result in a solution, initially try a larger positive value. If this does not result in an outcome, try a small negative value. The minimum value allowed is –1.

Notes:

If all periodic cash flows are the same, consider using the NPV function.

Example 1

Suppose you are planning for your daughter’s college education. She has just turned 13 and you expect she will begin college in 5 years. You have $75,000 to set aside in a savings account today and will add the bonus you receive from your employer at the end of each year. Because you believe your bonus will increase each year, you expect to be able to set aside $5,000, $7,000, $8,000, $9,000, and $10,000, respectively, at the end of each of the next 5 years. You think you will need to have $150,000 set aside for her education by the time your daughter reaches college age. Assume that cells B2 through G2 contain the amounts you will deposit, as negative amounts because they are outflows, beginning with the initial deposit (-75000, -5000, -7000, -8000, -9000, -10000). Cell H2 contains the amount you estimate will be required to fund your daughter’s college education, expressed as a positive number because it will be a cash inflow (150000). flows-range is B2:H2.

=IRR(B2:H2) returns 5.69965598016224%, the compound interest rate required so that the amounts deposited, together with annual interest, grow to $150,000 over the 5 years.

Example 2

Suppose you are presented with the opportunity to invest in a partnership. The initial investment required is $50,000. Because the partnership is still developing its product, an additional $25,000 and $10,000 must be invested at the end of the first and second years, respectively. Assume you place these cash outflows, as negative numbers, in cells B3 through D3. In the third year the partnership expects to be self-funding but not return any cash to investors (0 in E3). In the fourth and fifth years, investors are projected to receive $10,000 and $30,000, respectively (as positive numbers in F3 and G3). At the end of the sixth year, the company expects to sell and investors are projected to receive $100,000 (as a positive number in H3).

=IRR(B3:H3) returns 10.2411564203%, the compound annual interest rate earned (the internal rate of return) assuming all cash flows happen as scheduled.

Hi Bruce,

Are you trying to track the annualized performance of investments (as in investment portfolios) with irregular withdrawals by the investor, or calculate the rate of return on an investment (as in capital spending) that requires a series of uneven negative cash flows?

SG

Bruce,

It seems to me that Example 2, above fits you to a tee. The limitation is that you must decide on a payment interval and pretend that all your ins and outs occur on some fixed day of the month, quarter or whatever. As low as interest rates are in most places now, it really doesn't matter if you are compounding by day, month or quarter. You need a range for the entire projected life of the investment, and a cell in that range for each potential date of money transfer.

If the money goes out, give it a negative sign, if it comes in, it's positive.

"Annualizing" is trivial, as a rule. Just remember that whatever rate is calculated, that is for the interval you have assumed. If you assumed monthly transfers, muliply the rate result by 12 to annualize it.

Jerry

Hi Bruce,

I am trying to track annualized rate of return on an investment from which I make withdrawals and/or new deposits at irregular intervals during the year....I would hope that the same formula would work for a series of years and give me one-, three-, and five-year returns. I appreciate your reply.

This sounds as if you are trying to track the performance of a portfolio (perhaps a portfolio consisting of just one holding) from which you make withdrawals and to which you make contributions.

As I am sure you are aware, that is not a simple problem! However, here is one approach using what is called the Modified Dietz method, described here and here.

The setup in Numbers looks like this:

To make the formulas easier to follow, I turned on Numbers > Preferences > Use header names as labels. The first row is a Header Row and the last three rows (where the annualized returns appear) are Footer Rows.

Once the table is set up, you enter the EMV (Ending Market Value) for each month and the amount and day of the month of any contributions or withdrawals during that month (with both contributions and withdrawals entered as positive numbers). As you add months and the table expands, the formulas in the Footer Rows that calculate the annualized returns will adjust automatically. If you have fewer than 60 months you can wrap the errors in =IFERROR(<the formula>,"") to prevent the red triangle warning from displaying.

All the annualizing formulas do is use the values in the Index column to calculate a non-annualized return by dividing the ending index by the beginning index, then annualize that result by raising it to the power of 1/n where n is the number of years, and subtracting 1. OFFSET simply finds the right rows where the beginning and ending index values are located for that number of years.

(The index value in the hidden Row 50 in my example happen to be 1.22, which explains the 24% 1-year return.)

SG