We need an Excel formula that will determine the final amount in a series of cash flows so that a certain IRR rate is achieved.
Example: Series of 36 monthly cash flows.
1) -1000
2) +20
3) +20
*
*
*
35) +20
36) ???????
What is #36 that will give us 35% annual IRR?
This does not have to be a formula that fits all in one cell. You can have scratch pad calculations if you wish. These scratch pad can include guesses that are within +/- 80% of the final result. As various parts of the cash flow change the flow value in #36 will need to change so that the desired annual IRR is achieved. Number of cash flows is changeable. But is fixed for each individual spread sheet. i.e. We have 36 cash flows for this example. We may need to change that to 38 in the actual real application. The desired IRR is a variable in a cell.
## Deliverables
1) Complete and fully-functional working Excel formula in an Excel spreadsheet with cash flow example. Directions on how to change number of monthly cash flows.
## Platform
MS Excell 2003 SP2