Excel Spreadsheet Q&A

[Lynne]

This is the thread for all questions regarding the excel sheet. 

[Lynne]

Apparently there is a macro in the spreadsheet. When I click the macro, it asks for an input. What is that input supposed to be? 

[MidwesternActuary]

You are able to input 10 portfolios at a time. The first input is what portfolio to start with. The second input sn the portfolio to end with. When testing it out, you could just run "1", "1" to see how the output works. Enter portfolio 1 in cells D4:D6.

[Lynne]

When I input that, it props up an error message saying "Type mismatch." 

Where do you see the instructions of what values to enter into the macro? I don't see anything in the spreadsheet. 

[Lynne]

I tried it on a pc and I know understand how it works. Apparently my Mac doesn't show the prompt but it works the same! 

[LyActuary]

In the first tab C62, the cell about ending balance. 

What exactly is that? 

If I ran the macro, the goal seek automatically calculates the employee contribution and the ending balance ends up being 0. Is that how the goal seek works?

[Matthew Rashford]

I'm a little confused as to how the model works.  Are we supposed to input the asset class mixes in the "Model" sheet C67:C69?  Or in the "Results" sheet D4:M10?

[LyActuary]

we should input in the Results sheet. We can input however many scenarios we want. 

When we click the macro button, (idk if you can see the prompt, I couldn't) the first input is the scenario # to start with, and the second input is the scenario # to end with. 

After the macro is run, the active run cell highlighted by yellow reflects the scenario # the macro ended with and in my understanding, the corresponding employee contribution is calculated by goal seek automatically that is reflected on "Model" sheet. 

[deezydowntown]

I think how the macro technically works is that it takes your asset mix, stochastically generated the investment returns, and then figured out the contribution level to make the balance at the end of the fund 0.  It does this 1,000 times, and then it takes the mean/standard deviation/ etc. of those contribution levels.  Then it does that whole process again for the next asset mix.

[windhunter]

In the Model tab, only cell in Blue can be changed as they are assumptions. Dont touch the yellow or anything else. The Results tab is everything you need for the result. For different assets mix, you change it in Result tab. For more assets mix combinations, you add column between mix 9 and 10 (between M and N) and input the mix you want. The input for asset mix is row 3,4,5,6. The results are in row 10 to 21 in Result tab. There is "NO" result in Model tab. Dont refer to anything in Model tab as your result, The one show in Model tab is just one scenario run among 1000 scenarios.