0
$\begingroup$

I'm trying to simulate portfolio returns using Norm.inv function in excel. Inputs to the formula: Prob= Rand, Std dev= Historical, Mean= 5 year historical average.

Its easy to do this when you're assuming all your investments will receive at least some weight of your monthly installment. See below:

Simulating returns for SIP portfolio

Or when you're just simulating growth of your lumpsum portfolio. See below: enter image description here

The solution I'm seeking is what do I do for a portfolio where I have x wt as lumpsum and y wt as SIP? enter image description here

Improve this question
$\endgroup$
3
  • $\begingroup$ What is the formula currently in cell "G9" in the first example, and cell "F5" in your second example? You can copy paste here. $\endgroup$
    – Pontus Hultkrantz
    Nov 26, 2020 at 19:48
  • $\begingroup$ My question is, for the first example, don't you already have them both: a lumpsum which is your starting value 360829 (B1) and the SIP cashflow which is 9000 / month (B4)? $\endgroup$
    – Pontus Hultkrantz
    Nov 26, 2020 at 20:05
  • $\begingroup$ Hi @PontusHultkrantz, Sorry I've edited my post to correct the 2nd example. Formula in G9- =G8*(1+F9)+$B$4. in F5(2nd example)- =E4*(1+NORM.INV(RAND(),$B$3,$B$2)) The assumption is lump sum is rebalanced to certain portfolio wts and the SIP cash flow 9000 per month is going to be invested in the same proportion. But what if we wan't to keep certain investments as lump sum? For example 7 out of 9 get cash flows the other two are left to grow as they are? $\endgroup$
    – Swaraj_r
    Nov 27, 2020 at 3:28

1 Answer 1

Reset to default
1
$\begingroup$

Your formula in the first example is on the right track.

Standing at time step $i$, your value at next time step $i+1$ is $V_{i+1} = (V_{i} + c_i)(1+r_{i+1})$, i.e. your previous portfolio value plus an influx of $c_i$ in cash (SIP) are yielding a one step return of $r_{i+1}$. Explicitly you have

\begin{align} V_0 &= V_0 \\ V_1 &= (V_0 + c_0)(1+r_1) \\ V_2 &= (V_1 + c_1)(1+r_2) \\ V_{i+1} &= (V_{i} + c_i)(1+r_{i+1}) \\ \end{align}

So if your starting value (lump sum) is $V_0$, and there is a constant SIP payment starting from time step $1$ of $c$ then $c_0=0$ and $c_i=c$ for $i>0$.

Improve this answer
$\endgroup$
6
  • $\begingroup$ Thanks for that clarity! So now for my follow up question, while optimizing weights for the portfolio- how should I assign weights to a fund with future SIP cash inflows? I'm forecasting for 5 years so that makes it 60 monthly installments. Should I consider lumpsum weight today + 60 installments? $\endgroup$
    – Swaraj_r
    Nov 27, 2020 at 14:59
  • $\begingroup$ @Swaraj_r: What is the problem you are trying to optimize, what is the objective function? $\endgroup$
    – Pontus Hultkrantz
    Nov 28, 2020 at 21:03
  • $\begingroup$ Hulkrants. To minimize portfolio variance. 5 year return history, historical variance as in input for portfolio variance. $\endgroup$
    – Swaraj_r
    Nov 29, 2020 at 14:50
  • $\begingroup$ @Swaraj_r: The solution to that problem is called the Minimum Variance Portfolio (MVP). However, that is another problem and I suggest you post another question regarding that (feel free to link it here). Regarding this question about SIP vs lump sums, are you happy with the answer or do you need further clarifications before accepting it? $\endgroup$
    – Pontus Hultkrantz
    Nov 29, 2020 at 17:59
  • $\begingroup$ Hulkrants. Yes I'm aware of the concept of MVP. My confusion stems from the fact that weights change disproportionately when you're keeping some part of your portfolio as lump sum and the other as SIP. For eg: If I arrive at a MVP at point t0, I only have one weighted portfolio return(based on wts at t0) & one portfolio variance (based on wts at t0) but as I keep adding cashflows only to a part of the lumpsum portfolio at t0, I'm unsure if its fair to assume that the cashflows will earn the same return(based on wts at t0) as the wts keep change disproportionately with every cash flow. $\endgroup$
    – Swaraj_r
    Nov 30, 2020 at 6:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.