I doubt I titled this perfectly but here's the situation. I want to know what interest rate would need to be earned if I invested $20,000 per year for 15 years in order to arrive at exactly $1,000,000. I'd like to be able to show my progress in a spreadsheet on a yearly basis (basically at the end of year one, I have 22,000, at end of year two I have 46,000, etc.). I know how to do amortization schedules but I can't figure out how to reverse engineer them to achieve this result. As always, Chandoo peeps are my heroes. Thanks.
Calculating Required Interest Rate to Achieve Desired End Value on Series of Investments
- Thread starter Coldfish
- Start date
AlanSidman
Well-Known Member
Data Range
A | B | C | |
1 | 20000 | =A1+(A1*C1) | =IRR(A1:A16,0.05) |
2 | 20000 | =B1+A2+($C$1*(B1+A2)) | |
3 | 20000 | =B2+A3+($C$1*(B2+A3)) | |
4 | 20000 | =B3+A4+($C$1*(B3+A4)) | |
5 | 20000 | =B4+A5+($C$1*(B4+A5)) | |
6 | 20000 | =B5+A6+($C$1*(B5+A6)) | |
7 | 20000 | =B6+A7+($C$1*(B6+A7)) | |
8 | 20000 | =B7+A8+($C$1*(B7+A8)) | |
9 | 20000 | =B8+A9+($C$1*(B8+A9)) | |
10 | 20000 | =B9+A10+($C$1*(B9+A10)) | |
11 | 20000 | =B10+A11+($C$1*(B10+A11)) | |
12 | 20000 | =B11+A12+($C$1*(B11+A12)) | |
13 | 20000 | =B12+A13+($C$1*(B12+A13)) | |
14 | 20000 | =B13+A14+($C$1*(B13+A14)) | |
15 | 20000 | =B14+A15+($C$1*(B14+A15)) | |
16 | -1000000 | | |