derthorsten
New Member
- Joined
- Jul 13, 2015
- Messages
- 7
Hi Forum,
I am currently trying to build a dynamic bond investment portfolio with rolling tranches of monthly investments. Now, doing this in a static and unsexy way is not a challenge, however, I have difficulities in finding an automated formula based or vba solution.
I want to enter start date of the next investment and its investment horizon as well as amount & whether I want to roll over the investment:
start date 01.08.2015
tenor: 3Y
amount: $1000
rollover: yes
end date / rollover date?
monthly cash flows?
start of next tranche?
Excel should look up the horizon date (or rollover date), vlookup 3Y interest rates in a seperate table, apply it to the investment amount and generate monthly cash flows. Of course, excel should write the monthly dates on top of the cash flows (dynamic
). After 3Y, a new investment with forecasted interest rates (available on a seperate sheet) comes effective.
Is there a way to program this with formulas or vba?
I am currently trying to build a dynamic bond investment portfolio with rolling tranches of monthly investments. Now, doing this in a static and unsexy way is not a challenge, however, I have difficulities in finding an automated formula based or vba solution.
I want to enter start date of the next investment and its investment horizon as well as amount & whether I want to roll over the investment:
start date 01.08.2015
tenor: 3Y
amount: $1000
rollover: yes
end date / rollover date?
monthly cash flows?
start of next tranche?
Excel should look up the horizon date (or rollover date), vlookup 3Y interest rates in a seperate table, apply it to the investment amount and generate monthly cash flows. Of course, excel should write the monthly dates on top of the cash flows (dynamic
). After 3Y, a new investment with forecasted interest rates (available on a seperate sheet) comes effective. Is there a way to program this with formulas or vba?
