solve for interest rate of an annuity...

[jjst34]

I need some help solving this formula, I am trying to get the rate...
Assume interest is compounded monthly (i'd then like to see the formula
and be able to change from monthly to daily compounding so I can see
the differences)... here it is..

initial investment
2/1/04 125,000

payouts
2/1/11 - 25,000
2/1/18 - 25,000
8/1/17 - 30,000
8/1/18 - 30,000
8/1/19 - 30,000
8/1/20 - 30,000
8/1/17 thru 7/1/20 $400 monthly in addition to above yearly amount...
4/1/24 - 10,000
4/1/29 - 87,500

this will bring it down to 0... Any help w/ this is very much
appreciated. Thanks!

 

[Norman Harker]

Hi jjst34!

Use XIRR (in the Analysis ToolPak) and it will return the Annual
Effective rate of return of amounts tied to dates.

See Help for an explanation.

=XIRR(values,dates,guess)
Returns the annual effective interest rate for a schedule of cash
flows received at specified dates

Once you have the Annual Effective rate you can find the Monthly
effective equivalent using:

=(1+AnnEff)^(1/12)-1


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[jjst34]

Thanks a lot Norman.. Very helpful