Hi, clever people.
I am building a financial table to find the payoff value for a financial instrument that solves for the cash flow needed in each future period, on a discrete basis, such that an XIRR target is satisfied. Basically, I want to know what the dollar hurdle is to fully pay-off the instrument in each future period when considering all previous payments and investments. Some considerations that make this a challenge:
Soooo... I am thinking one of three things:
Thoughts? If so, I'd appreciate guidance on any of 1-3, above. Sample table provided below so you can better see what I'm trying to do.
Thanks so much for your help!
<tbody>
</tbody>
I am building a financial table to find the payoff value for a financial instrument that solves for the cash flow needed in each future period, on a discrete basis, such that an XIRR target is satisfied. Basically, I want to know what the dollar hurdle is to fully pay-off the instrument in each future period when considering all previous payments and investments. Some considerations that make this a challenge:
- Variable cash flows (both in and out) (see Row 3)
- Variable timing of cash flows (can be at any time during future months) (see Row 4)
- Solving for data in one row with many columns (in the case of my data, 64 columns) (see Row 7)
- Columns are monthly
- A simple .GoalSeek in a VBA script works, except that I'll need to build a full 64x64 matrix by hand in order to isolate for each of the 64 .GoalSeek actions (seems calc. heavy/inelegant)
- Existing functions (IRR, PMT, etc.) won't work since cash flows and timing/periods are variable
- XIRR doesn't allow ranges to be non-contiguous (so you can't add a comma to the range, and things like OFFSET won't work either).
Soooo... I am thinking one of three things:
- Use a .GoalSeek VBA script across the columns, but add script such that the resulting value in each discrete period is cut-and-pasted to another location before moving the .GoalSeek action to the next column. Without the cut/paste, .GoalSeek will stop at the first column and/or return an error. .GS results cannot be cumulative. What does this script look like?
- Invert the XIRR function using =LOG(). Am I in error on this, or would one be able to write a log function that solves for the unknown variable?
- I'm overcomplicating this problem and the solution is far more straightforward.
Thoughts? If so, I'd appreciate guidance on any of 1-3, above. Sample table provided below so you can better see what I'm trying to do.
Thanks so much for your help!
A1 | B | C | D | E | F | BN | |
2 | Component | Row Contents | Jan. 2017 | Feb. 2017 | Mar. 2017 | // | Apr. 2022 |
3 | Investment | [Variable] | $(75,000) | $ - | $(5,000) | // | $ - |
4 | Payments Rec'd | [Variable] | - | 14,195 | 922 | // | 1,905 |
5 | Net for Period | =SUM(D3:D4) | (75,000) | 14,195 | (4,788) | // | 1,905 |
6 | IRR Target | [Variable] | 15% | // | |||
7 | Implied Cash Flow Needed in This Period to Hit IRR Target | [Variable] Solve for This --> | - | - | - | // | - |
8 | Total Implied CF for Period | =D5+D7 | $ - | $ - | $ - | // | $ - |
9 | XIRR Return | =XIRR($D$8:D$8,$D$2:D$2) (carried forward to each period) | - % | - % | - % | // | - % |
10 | Solved CF Formula? | =LOG(??) |
<tbody>
</tbody>