|
|
Post by robberbaron on Oct 11, 2015 8:53:24 GMT
You could model the market risk and correlation through the formula in this link: goddardconsulting.ca/option-pricing-monte-carlo-basket.html. Here x i could represent the uncorrelated evolution of the price (i.e. return) of the property i drawn from a normal distribution. ε i would be the correlated evolution of the price for property i. If the property price (i.e. ε i * P(0)) falls below a certain level (e.g. the initial price or some other level) then the associated loan is in default and the loss is max(loan amount - ε i - PF, 0). You could assume the PF replenishes at a certain rate between each period. Adding correlation would mean that the risk of large losses would be higher than if risk of default of the loans were independent. The VBA code for the Cholesky factorization can be easily found online. |
|
eddie
i have put up with a great deal from the likes of you people, a very great deal....
Posts: 63 
Likes: 21
|
Post by eddie on Oct 11, 2015 9:00:54 GMT
Im in the construction industry, mainly commercial new builds, when work slows down we pick up residential work but right now even that is scarce, what does that do to the figures? SS hasnt been around long enough for us to accurately predict a down turn or contraction maybe?
|
|
shimself
Member of DD Central
Posts: 2,563
Likes: 1,171
|
Post by shimself on Oct 11, 2015 13:09:51 GMT
<abbr>follow</abbr>
|
|
|
|
Post by solicitorious on Oct 12, 2015 17:32:34 GMT
You could model the market risk and correlation through the formula in this link: goddardconsulting.ca/option-pricing-monte-carlo-basket.html. Here x i could represent the uncorrelated evolution of the price (i.e. return) of the property i drawn from a normal distribution. ε i would be the correlated evolution of the price for property i. If the property price (i.e. ε i * P(0)) falls below a certain level (e.g. the initial price or some other level) then the associated loan is in default and the loss is max(loan amount - ε i - PF, 0). You could assume the PF replenishes at a certain rate between each period. Adding correlation would mean that the risk of large losses would be higher than if risk of default of the loans were independent. The VBA code for the Cholesky factorization can be easily found online. Thanks for that. All a bit complicated! The code at the moment, using cell formula for each loan (slightly different for the four 2nd charges), is =IF(RAND()<$H$3,A3,IF(RAND()<$D$1,A3*MAX(0,1-(1-$E$1)/B3),0)) where $H$3 is the prob of Total Loss (0.10% being tested currently) A3 is loan value (repeated down for each loan) $D$1 is prob of default (the D%, 50% being tested currently) $E$1 is %loss given a default (the L%, 50% being tested currently) B3 is LTV% for the loan (repeated down for each loan) These outputs are then summed for all loans and the Provision Fund deducted from the total (if total > PF, else 0 overall loss). A bit of VB (under a button) copies this value to another column 10000 times, which automatically fires a repeat of the RAND()s for the next iteration of the Monte Carlo. At the end, the resultant values are then averaged and divided by the loanbook total to calculate the average loss, and histogram also created. Not sure whether your correlation formula is appropriate in these circs.
|
|
|
|
Post by robberbaron on Oct 12, 2015 18:48:26 GMT
Thanks for that. All a bit complicated! The code at the moment, using cell formula for each loan (slightly different for the four 2nd charges), is =IF(RAND()<$H$3,A3,IF(RAND()<$D$1,A3*MAX(0,1-(1-$E$1)/B3),0)) where $H$3 is the prob of Total Loss (0.10% being tested currently) A3 is loan value (repeated down for each loan) $D$1 is prob of default (the D%, 50% being tested currently) $E$1 is %loss given a default (the L%, 50% being tested currently) B3 is LTV% for the loan (repeated down for each loan) These outputs are then summed for all loans and the Provision Fund deducted from the total (if total > PF, else 0 overall loss). A bit of VB (under a button) copies this value to another column 10000 times, which automatically fires a repeat of the RAND()s for the next iteration of the Monte Carlo. At the end, the resultant values are then averaged and divided by the loanbook total to calculate the average loss, and histogram also created. Not sure whether your correlation formula is appropriate in these circs. Not sure you could retrofit correlation into this model. I would instead generate n normally distributed random variables (using Box-Muller) for the n properties. This study of the UK housing market suggest the historical mean is 0.025 and the standard deviation 0.028 (also suggests the returns are not really normally distributed but it's still better than a uniform distribution). For the correlation coefficient I would assume that all the property prices have a very high correlation (e.g. somewhere between 0.80 and 0.90, higher for properties from the same borrower). Then compute the matrix R using Cholesky decomposition and use it to compute the returns ε i for all the property prices. Once you have that you can multiply the return by the property price in the previous period to get the current price and use some rule to determine if this causes the loan to be in default (e.g. if the price is lower than when the loan was contracted). You can then use the price you have computed to get the final loss. Note that you no longer need as input the loss given a default or the probability of default.
|
|
|
|
Post by solicitorious on Oct 12, 2015 20:00:46 GMT
Thanks for that. All a bit complicated! The code at the moment, using cell formula for each loan (slightly different for the four 2nd charges), is =IF(RAND()<$H$3,A3,IF(RAND()<$D$1,A3*MAX(0,1-(1-$E$1)/B3),0)) where $H$3 is the prob of Total Loss (0.10% being tested currently) A3 is loan value (repeated down for each loan) $D$1 is prob of default (the D%, 50% being tested currently) $E$1 is %loss given a default (the L%, 50% being tested currently) B3 is LTV% for the loan (repeated down for each loan) These outputs are then summed for all loans and the Provision Fund deducted from the total (if total > PF, else 0 overall loss). A bit of VB (under a button) copies this value to another column 10000 times, which automatically fires a repeat of the RAND()s for the next iteration of the Monte Carlo. At the end, the resultant values are then averaged and divided by the loanbook total to calculate the average loss, and histogram also created. Not sure whether your correlation formula is appropriate in these circs. Not sure you could retrofit correlation into this model. I would instead generate n normally distributed random variables (using Box-Muller) for the n properties. This study of the UK housing market suggest the historical mean is 0.025 and the standard deviation 0.028 (also suggests the returns are not really normally distributed but it's still better than a uniform distribution). For the correlation coefficient I would assume that all the property prices have a very high correlation (e.g. somewhere between 0.80 and 0.90, higher for properties from the same borrower). Then compute the matrix R using Cholesky decomposition and use it to compute the returns ε i for all the property prices. Once you have that you can multiply the return by the property price in the previous period to get the current price and use some rule to determine if this causes the loan to be in default (e.g. if the price is lower than when the loan was contracted). You can then use the price you have computed to get the final loss. Note that you no longer need as input the loss given a default or the probability of default. Definitely beyond my stats knowledge. Some kind of autoregressive time-series? My model is basically a Monte Carlo of Bernoulli trials of loans which all have the same (user-defined D%) probability of failure. The absolute amounts lost will vary by loan (via user-defined L%), but the total lost across the loanbook (minus the PF) is a good proxy for risk, assuming a "standard" investment profile. I've enhanced the model to cope with any investment profile. For each iteration of the MC, calculate the overall %loss (after deducting the PF) based on the loans that actually failed, and apply this loss rate to your own investment portfolio (just to each loan you hold which failed.) Sum these values for each of the 10000 iterations, then calculate average loss and create histogram, etc. based on your total investment. I'll make the spreadsheet presentable and post it sometime...
|
|
|
|
Post by solicitorious on Oct 13, 2015 22:15:37 GMT
Voila! [ SSMC.xls removed 5/4/16 and new version posted downthread]Read the notes in the red tab, and have fun.  |
|
registerme
Member of DD Central
Posts: 6,624
Likes: 6,437
|
Post by registerme on Oct 13, 2015 23:51:53 GMT
Those are obviously space filler numbers, right, solicitorious, I mean you wouldn't want to do what some others have done recently (?) and just put your portfolio out there? I'm a massive fan of sharing tools and knowhow, benefiting from it, critiquing it, improving it etc, all marvellously open source. But I'd advise all to avoid putting any of their own, personal, data in it...... |
|
|
|
Post by solicitorious on Oct 14, 2015 0:52:10 GMT
Lol
my portfolio changes so often you'd need a quantum computer to keep track of it.
|
|
|
|
Post by solicitorious on Oct 21, 2015 13:40:02 GMT
Adding PBL64 to the mix
for D=50%, L=50% (uniform), T=0.10% (1 in a 1000 chance of a total loss)
Model says:
Chance of any loss 100.00%, no loss 0.00%
loss <0.5% 0.00%
loss >0.5% 100.00%
loss >1% 99.98%
loss >2% 99.74%
loss >3% 98.62%
loss >5% 86.95%
loss >10% 10.42%
loss >15% 0.12%
loss >20% 0.00%
loss >30% 0.00%
loss >40% 0.00%
loss >50% 0.00%
overall loss 7.38%, including times when there's no loss
average loss 7.38%, if there is a loss
Comparison with when there's no PF
Chance of any loss 100.00%, no loss 0.00%
loss <0.5% 0.00%
loss >0.5% 100.00%
loss >1% 100.00%
loss >2% 100.00%
loss >3% 99.98%
loss >5% 98.62%
loss >10% 38.24%
loss >15% 0.49%
loss >20% 0.01%
loss >30% 0.00%
loss >40% 0.00%
loss >50% 0.00%
overall loss 9.38%, including times when there's no loss
average loss 9.38%, if there is a loss
|
|
Liz
Member of DD Central
Posts: 2,426
Likes: 1,297
|
Post by Liz on Apr 4, 2016 13:14:02 GMT
Adding PBL64 to the mix for D=50%, L=50% (uniform), T=0.10% (1 in a 1000 chance of a total loss) Model says: Chance of any loss 100.00%, no loss 0.00% loss <0.5% 0.00% loss >0.5% 100.00% loss >1% 99.98% loss >2% 99.74% loss >3% 98.62% loss >5% 86.95% loss >10% 10.42% loss >15% 0.12% loss >20% 0.00% loss >30% 0.00% loss >40% 0.00% loss >50% 0.00% overall loss 7.38%, including times when there's no loss average loss 7.38%, if there is a loss Comparison with when there's no PF Chance of any loss 100.00%, no loss 0.00% loss <0.5% 0.00% loss >0.5% 100.00% loss >1% 100.00% loss >2% 100.00% loss >3% 99.98% loss >5% 98.62% loss >10% 38.24% loss >15% 0.49% loss >20% 0.01% loss >30% 0.00% loss >40% 0.00% loss >50% 0.00% overall loss 9.38%, including times when there's no loss average loss 9.38%, if there is a loss Could you please re-run the analysis,using the current loan book, thanks.
|
|
Liz
Member of DD Central
Posts: 2,426
Likes: 1,297
|
Post by Liz on Apr 4, 2016 13:34:05 GMT
Is it possible to factor in a 100% loss on the loans with 2nd charges in a separate analysis?
|
|
|
|
Post by solicitorious on Apr 5, 2016 4:04:11 GMT
Is it possible to factor in a 100% loss on the loans with 2nd charges in a separate analysis? I'm not quite sure what you mean by the above statement. Perhaps you could clarify? Anyhow, for the current loan book* for D=50%, L=50% (uniform), T=0.10% (1 in a 1000 chance of a total loss) Model says: Chance of any loss 100.00%, no loss 0.00% loss <0.5% 0.00% loss >0.5% 100.00% loss >1% 100.00% loss >2% 99.98% loss >3% 99.80% loss >5% 95.70% loss >10% 25.27% loss >15% 0.25% loss >20% 0.00% loss >30% 0.00% loss >40% 0.00% loss >50% 0.00% overall loss 8.61%, including times when there's no loss average loss 8.61%, if there is a loss Comparison with when there's no PF Chance of any loss 100.00%, no loss 0.00% loss <0.5% 0.00% loss >0.5% 100.00% loss >1% 100.00% loss >2% 100.00% loss >3% 100.00% loss >5% 99.80% loss >10% 60.51% loss >15% 2.11% loss >20% 0.00% loss >30% 0.00% loss >40% 0.00% loss >50% 0.00% overall loss 10.61%, including times when there's no loss average loss 10.61%, if there is a loss A seemingly moderately significant change since 6 months ago. I would put this down mostly to the introduction of some very large loans, DFL001, PBL087 among several others. Default and a 50% loss in any of these could practically wipe out the provision fund in one go, impacting %recovery rates across other smaller defaulted loans, and the overall histogram. [* there is a minor issue with PBL006 which partially repaid recently, but savingstream don't appear to have updated the loan amount, LTV or PF to take account of this. Should have a negligible impact on the above figures, however]
|
|
|
|
Post by solicitorious on Apr 5, 2016 9:06:50 GMT
New version of Excel spreadsheet. SSMC2.xls (154.5 KB) |
|
|
|
Post by Deleted on Apr 5, 2016 10:16:29 GMT
All very interesting, but for practical purposes completely meaningless, as the results are entirely dependent on the values given to the base parameters, which is complete guess work and will vary significantly depending on the specifics of each individual loan and external macro factors.
|
|
