The Compound Rate Equation

Suppose we have transaction amounts A₀, A₁, ..., A_n (positive or negative) invested for durations t₀, t₁, ..., t_n (years) that grow (or shrink), in total, to a positive, end amount B. Then compound annual rate of growth, r, corresponding to this situation, must satisfy the equation

A0 * EXP( r * t0 ) + A1 * EXP( r * t1 ) + ... + An * EXP( r * tn )

=

B

This rate equation can not generally be solved for r in closed form except when there is only a single transaction. Then the equation reduces to one of the form

A * EXP( r * t )

=

B

and the solution is

r

=

( 1 / t ) * LN( B / A ),

where LN is the natural logarithm function.

An Explicit Solution (in Excel)

When we say that the rate equation above can not be solved in closed form we mean that there is no finite formula expressible in terms of elementary functions and algebraic operations that gives the solution. On the other hand there may be one or more functions available on a specific spread sheet that allow an explicit solution.

For example the solution rate, r, can be expressed explicitly in terms of the Microsoft Excel function XIRR. If we denote the amount and date arrays by A and D, respectively,

	A	=	{ A0, A1, ..., An, -B },
	D	=	{ d0, d1, ..., dn, dB, },

then the solution rate is given explicitly as

r

=

(365.25/365) * LN( 1 + XIRR( A, D, r0 ) ),

where r₀ is a suitable initial guess at the rate. In our particular example,

	A	=	{ 21,327.52, 10,000.00, -15,000.00, 10,000.00, -49,208.62 },
	D	=	{ 12/31/91, 5/17/93, 6/13/94, 2/13/95, 12/31/95 },

and the XIRR rate formula above (with r₀ = 0) gives the solution r = 16.68185%.