Completing the actually simulation that will provide the list of the sample number and the list of NPV's. Can you explain how to get the results of the Monte Carlo Simulation?

Transcribed Image Text:

1 Purchase 2 Purchase price of car 4 Lease 5 Down payment 6 Monthly payment 7 Payment term (months) 8 Security deposit 9 Charge mile 10 Purchase price at end of lease 11 Average Mileage driven. 12 Average mileage /mth 13 Mileage 14 Interest rate 15 Realizable value 16 Threshold Mileage 17 18 19 20 21 23 24 11 S 12 14 P 16 20 27 28 19 1 Month 0 - 7 9 10 11 12 13 14 P 10 10 " 11 12 12 WA 13 M 14 15 15 W 16 17 27 20 20 21 21 22 23 24 6 2 =E20+F19 10 -SR$12*IN/RAND()) -F21+F20 =E21+F20 18 19 -$B$12 LN(RAND()) 19 $B$12*IN/BAND() =$B$12 LN(RAND()) -F22+521 =E22+F21 -$B$12 LN(RAND()) =23+F22 =$8$12 LN(RAND()) =E24+F23 -$B$12 LN (RAND()) =E25+F24 =$B$12*LN(RAND()) =E26+F25 --$B$12 LN (RAND()) =E27+F26 1 -18574.870 2 -19385.933 3 -18314.760 4 -17823.803 . $5806.002 -17506.903 7 4 8 11 12 13 14 12 P 12 15 13 A 9 D 10 ** 11 14 14 12 15 16 16 Mileage =$B$12 LN(RAND()) =E3 =$B$12 LN(RAND()) =E4+F3 =$B$12 LN(RAND()) =E5+F4 $B$12 LN(RAND()) =E6+F5 PANDAI =$B$12 LN(RAND()) =E7+F6 COCORANO ERAEZ -$B$12 LN(RAND()) =E8+F7 12/BAND/11 -EQ-5D $B$12 LN(RAND()) =E9+F8 COCONU 1015 =$B$12 LN(RAND()) =E10+F9 Andesti 110 --$B$12 LN(RAND()) =E11+F10 CRARANDA --$B$12*LN(RAND()) =E12+F11 NANO/ -12-17 -513-11 =$B$12 LN (RAND()) =E13+F12 SDC12N/BAND/-5144512 =$B$12 LN (RAND()) =E14+F13 RAVDAND/-515-514 =$B$12*LN(RAND()) =E15+F14 $B$121NWAND/11 -5164515 =$B$12 LN(RAND()) =E16+F15 $B$12*IN/RANDO) 17+16 =-$B$12*LN(RAND()) =E17+F16 GREINBAND/11 -519-517 =$B$12*LN(RAND()) =E18+F17 -17719.922 17710022 GREINIRANOX-519-518 --$B$12 LN(RAND()) =E19+F18 -CR611NBAND/1-5204519 -$B$12 LN(RAND()) www -18701.296 Setti -18479.250 com -18970.637 www.wor -18936.447 00 17 17 -17970.001 18 -19753.951 19 -18016.249 20 -18310.843 21 -17796.992 22 -17927 420 15 13 -18395.900 1.com -18533.688 10174934 -18174.934 19726 590 -18236.589 19062 063 -18063.082 -17170.022 Sample 2 Number SKS28 3 4 5 6 7 8 16 14 17 15 1 -18097.076 2 -18074.074 43240 3 -18164.035 4 -18267.836 5 -18148.436 -17919.622 7500 458 24 550 0.24 29732 25000 -811/24 Results of Monte Carlo Simulation Sample Number SHS28 6 7 -18270.916 8 -19454.259 9 -17976.283 10 -18287.870 11 -18229.493 12 -18083.780 =NORMINV(RAND().8.5%,1%) -B10-MAX(0,E3-30000)*89 -18284.855 -18067.705 -18110.372 18 16 -18192.008 19 17 -17966.616 20 18 -18261.984 21 19 -18129.281 22 20 -18169.362 23 21 -18947.639 24 22 -18438.251 25 22 18700 650 30000 F Cumulative Mileage G Cashflow =-82 C Month 0 1 2 E 3 4 5 6 7 8 D 9 10 11 12 13 14 15 16 17 18 19 20 21 Mileage =$B$12 LN (RAND()) =E3 =$B$12 LN (RAND()) =E4+F3 --$B$12 LN(RAND()) =ES+F4 =$B$12 LN (RAND()) =E6+F5 =-$B$12 LN (RAND()) =E7+F6 --$B$12 LN (RAND()) =EB+F7 =-$B$12 LN (RAND()) =E9+F8 --$B$12 LN(RAND()) =E10+F9 -$B$12 LN(RAND()) -E11+F10 =$B$12 LN (RAND()) =E12+F11 =$B$12 LN (RAND()) =E13+F12 $B$12 LN(RAND()) -E14+F13 --$B$12 LN(RAND()) =E15+F14 =-$B$12 LN(RAND()) =E16+F15 =-$B$12 LN (RAND()) =E17+F16 =$B$12 LN (RAND()) =E18+F17 =-$B$12 LN(RAND()) =E19+F18 =$B$12 LN (RAND()) =E20+F19 =-$B$12 LN(RAND()) =E21+F20 =-$B$12 LN(RAND()) =E22+F21 --$B$12 LN (RAND()) =E23+F22 =$B$12 LN(RAND()) =E24+F23 H Purchase A B C D Results of Monte Carlo Simulation 14607 64/160614/12/012 =G12/(1+$B$14/12)^SD12 -622/(1+$B$14/12)^$022 =G22/(1+$B$14/12)^$D22 -623/(1+$B$14/12)^5D23 -G23/(1+58514/12)^SD23 =G24/(1+$B$14/12)^$D24 -G25/(1+$B$14/12)^$D25 =G26/(1+$B$14/12)^$D26 -B10-MAX(C-G27/(1+$B$14/12)^$D27 NPV = =SUM(H3:H27) DCF =G3/(1+$B$14/12)^$D3 -G4/(1+$B$14/12)^SD4 =G5/(1+$B$14/12)^$D5 -G6/(1+$B$14/12)^SD6 =G7/(1+$B$14/12)^$D7 -G9/11+59514/121658 -G8/(1+$B$14/12)^SD8 -69/11-60611/12) -G9/(1+$B$14/12)^$D9 tot i otic vic $8$6-IF(F9>$B$16, F9-MAX(F8,$B$16),0) "$B$9 CDCE JEJC10NCDC16 E10.MAVIER SOSIS) IRSO =G10/(1+$B$14/12)^$D10 --$B$6-IF(F10>=$BS16,F10-MAX(F9,$B$16),0) *$B$9 -630/60634/160 633/11-60614/1214601 =G11/(1+$B$14/12)^$D11 6066 IE/ESDC1G E11 MAYISO SRCIC) OLTER $8$6-IF(F11-$B$16,F11-MAX(F10,S$B$16).0)*$B$9 CDC JE/13-0616 E12 MAXIEST CR6161 16 -$B$6-IF(F12=SBS16,F12-MAX(F11,$B$16),0)*$B$9 33/36064/1216012 CDCE JE/E12R16 E12 MAYIE12 6616) CpCo -G13/(1+$B$14/12)^$D13 --$B$6-IF(F13-$B$16,F13-MAX(F12,$B$16),0)*$B$9 Simulation Stats 1000 repetitions 8 seconds Cumulative Mileage Cashflow G ali.6063/116014 =G14/(1+$B$14/12)^$D14 -615/(1+$B$14/121015 -G15/(1+$B$14/12) "$D15 6/1603A/1216016 -G16/(1+$B$14/12)^$D16 -617/11-60514/12/2017 -G17/(1+$B$14/12)^$017 CDC6.JE/E14CD616 ETA-MAXIE12 CD616) 01+COCO $B$6-IF(F14>=$B$16,F14-MAX(F13,$B$16),0)*$B$9. CDSG JELE15-CR$16 515 MAVI1A SP16) IXSp.co -$8$6-IF(F15>-$BS16,F15-MAX(F14,$B$16).0)*$B$9 CDC6.JE/E16-CR$16 516 MAYICIS CR161 1+CPco $B$6-IF(F16>=$B$16,F16-MAX(F15,$B$16),0)*$B$9 6066.JE/E17-60816 17.MAVIE16 606161 16 $B$6-IF(F17>=$B$16,F17-MAX(F16,$B$16).0)*$B$9 19/11160614/12/19 CDC615/519-60616 512.MAY/E17 SP161 160 =G18/(1+$B$14/12)^$D18 --$8$6-IF(F18>=$B$16,F18-MAX(F17,$B$16),0)*$B$9 -619/11460614/12146019 -G19/(1+$B$14/12)^$D19 -620/11-58514/12 =G20/(1+$B$14/12)^$D20 -621/(1+$B$14/12)^021 -G21/(1+$B$14/12)^$D21 Simulation Stats =-82 C -21400 1000 repetitions 7 seconds G =-(85+88) --SBS6-IF(F4> SBS16,F4-MAX(F3.$B$16),0) $B$9 =-$8$6-IF(F5>$B$16,F5-MAX(F4,$B$16),0)*$B$9 -$B$6-IF(F6>=$B$16,F6-MAX(F5,$B$16),0)*$B$9 -$B$6-IF(F7>-$B$16,F7-MAX(F6,$B$16),0)*$B$9 $B$6-IF(F8>=$B$16,F8-MAX(F7,$B$16),0)"$B$9 Summary Statistics Average -18299.008 SD Max Min 638.7330 -16128.007 -21309.469 H CD66-1/10-6S16 519-MAYIE19 GR$161 1650 SBS6-IF(F19>=SBS16,F19-MAX(F18,$B$16).0) *$B$9 -CR$6.15/20-p$16 520 MAYIE10 606161 160o -$B$6-IF(F20-$B$16,F20-MAX(F19,$B$16),0)*$B$9 SBS6-JE/F21>-SBS16 F21-MAY(520 $B$16) *SBSO --$B$6-IF(F21>-SBS16,F21-MAX(F20,$B$16),0) *$B$9 Purchase =SBS6-IF(F22>=$B$16.F22.MAX(E21 $B$16101*$B$9 -$B$6-IF(F22>=$B$16,F22-MAX(F21,$B$16),0) *$B$9 SBS6-IF(F23>=$B$16,F23-MAX(F22,SB$16).0)*$B$9 -$8$6-IF(F24>=$B$16,F24-MAX(F23,$B$16),0) *$B$9 --$B$6-IF(F25>$B$16,F25-MAX(F24,$B$16).0)*$B$9 --$B$6-IF(F26>=$B$16,F26-MAX(F25,$B$16),0)*$B$9 =-$B$6-IF(F27>=$B$16,F27-MAX(F26,$B$16),0) *$B$9 -20400 DCF =G3/(1+$B$14/12)^$D3 =G4/(1+$B$14/12)^$D4 --$B$6-IF(F4>=$B$16,F4-MAX(F3,$B$16).0) *$B$9 -G5/(1+$B$14/12)^$05 --$B$6-IF(F5>-$B$16,F5-MAX(F4,$B$16).0) "$B$9 -G6/(1+$B$14/12)^$D6 --$B$6-IF(F6>=$B$16,F6-MAX(F5, $B$16),0) "$B$9 -G7/(1+$B$14/12)^$D7 --$B$6-IF(F7>=$B$16,F7-MAX(F6, $B$16).0)*$B$9 -G8/(1+$B$14/12) 08 --$B$6-IF(F8>=$B$16,F8-MAX(F7,$B$16),0) "$B$9 -G9/(1+$B$14/12)^$D9 --$B$6-IF(F9>=$B$16, F9-MAX(F8,$B$16),0) *$B$9 -G10/(1+$B$14/12)^$D10 --$B$6-IF(F10>-$B$16,F10-MAX(F9,$B$16),0) *$B$S -G11/(1+$B$14/12)^$D11 --$B$6-IF(F11>=$B$16,F11-MAX(F10,$B$16).0) $BS -G12/(1+$B$14/12)^$D12 =-$B$6-IF(F12>=$B$16,F12-MAX(F11,$B$16),0)*$B$ -G13/(1+$B$14/12)^$D13 =-$B$6-IF(F13>=$B$16,F13-MAX(F12,$B$16),0) $B$ -G14/(1+$B$14/12)^$D14 --$B$6-IF(F14>=$B$16,F14-MAX(F13,$B$16).0) $B$ -G15/(1+$B$14/12)^$D15 -$B$6-IF(F15>=$B$16,F15-MAX(F14,$B$16),0) $B$ =G16/(1+$B$14/12)^$D16 --$B$6-IF(F16-$B$16,F16-MAX(F15,$B$16),0) $B$ -G17/(1+$B$14/12)^$D17 --$B$6-IF(F17>-$B$16,F17-MAX(F16,$B$16),0)*$B$ -G18/(1+$B$14/12)^$D18 --$B$6-IF(F18-$B$16, F18-MAX(F17,$B$16),0)*$B$ -G19/(1+$B$14/12)^$D19 =$B$6-IF(F19 $B$16,F19-MAX(F18,$B$16),0) $B$ -G20/(1+$B$14/12)^$D20 --$B$6-IF(F20-$B$16,F20-MAX(F19,$B$16),0) $B$ =G21/(1+$B$14/12)^$D21 =$B$6-IF(F21>=$B$16,F21-MAX(F20,$B$16),0) $BS -G22/(1+$B$14/12)^$D22 $B$6-IF(F22>=$B$16,F22-MAX(F21,$B$16),0) $B$ -G23/(1+$B$14/12)^$D23 =-$B$6-IF(F23>=$B$16,F23-MAX(F22,$B$16),0)*$B$ -G24/(1+$B$14/12)^$D24 --$B$6-IF(F24>=SBS16,F24-MAX(F23,$B$16),0) $B$ Cashflow 1 -22000 Lease NPV = Histogram of $H$28 -19400 J -18400 Summary Statistics. Average -18273.093 SD Max 398.4021 -17825.588 -21949.809 Min Notes -21000 -17400 =-(85+88) -20000 L DCF =J3/(1+$B$14/12)^$D3 =J4/(1+$B$14/12)^SD4 =J5/(1+$B$14/12)^$D5 -J6/(1+$B$14/12)^$D6 =J7/(1+$B$14/12)^$D7 10/11160614/11 =J8/(1+$B$14/12)^$DB -10/11260614/421 =19/(1+$B$14/12)^$D9 -110/11+514/12/2016 =J10/(1+$B$14/12)^SD10 -111/(1+68514/12) =J11/(1+$B$14/12)^$D11 152/11160614/201 =J12/(1+$B$14/12)^$D12 -112/11-60514/12146012 =J13/(1+$B$14/12)^$D13 -114/11+50514/12165014 =J14/(1+$B$14/12)^$D14 -115/11+$B$1/A/12015 =J15/(1+$B$14/12)^$D15 -116/11+$B$14/12165016 =J16/(1+$B$14/12)^$D16 -117//1460514/12/17 =J17/(1+$B$14/12)^SD17 -119//1+59514/121/5018 =J18/(1+$B$14/12)^$D18 -119//1+59514/12)65019 =J19/(1+$B$14/12)^$D19 -120/11+59514/121020 =J20/(1+$B$14/12)^$D20 -121//1+R$14/12145021 =J21/(1+$B$14/12)^$D21 -122/(1+$B$14/12165022 =122/(1+$B$14/12)^$D22 -123/(1+$B$14/12)^SD23 -124/(1+$B$14/12)^$D24 -125/(1+$8$14/12)^SD25 -J26/(1+$B$14/12)^$D26 -J27/(1+$B$14/12)^SD27 =SUM(K3:K27) N -16400 Histogram of $K$28 Cashflow 0 Notes -19000 M N Lease -18000 Q O 1 Purchase 2 Purchase price of car 4 Lease 5 Down payment 6 Monthly payment 7 Payment term (months) 8 Security deposit 9 Charge mile 10 Purchase price at end of lease 11 Average Mileage driven. 12 Average mileage /mth 13 Mileage 14 Interest rate 15 Realizable value 16 Threshold Mileage 17 18 19 20 21 23 24 11 S 12 14 P 16 20 27 28 19 1 Month 0 - 7 9 10 11 12 13 14 P 10 10 " 11 12 12 WA 13 M 14 15 15 W 16 17 27 20 20 21 21 22 23 24 6 2 =E20+F19 10 -SR$12*IN/RAND()) -F21+F20 =E21+F20 18 19 -$B$12 LN(RAND()) 19 $B$12*IN/BAND() =$B$12 LN(RAND()) -F22+521 =E22+F21 -$B$12 LN(RAND()) =23+F22 =$8$12 LN(RAND()) =E24+F23 -$B$12 LN (RAND()) =E25+F24 =$B$12*LN(RAND()) =E26+F25 --$B$12 LN (RAND()) =E27+F26 1 -18574.870 2 -19385.933 3 -18314.760 4 -17823.803 . $5806.002 -17506.903 7 4 8 11 12 13 14 12 P 12 15 13 A 9 D 10 ** 11 14 14 12 15 16 16 Mileage =$B$12 LN(RAND()) =E3 =$B$12 LN(RAND()) =E4+F3 =$B$12 LN(RAND()) =E5+F4 $B$12 LN(RAND()) =E6+F5 PANDAI =$B$12 LN(RAND()) =E7+F6 COCORANO ERAEZ -$B$12 LN(RAND()) =E8+F7 12/BAND/11 -EQ-5D $B$12 LN(RAND()) =E9+F8 COCONU 1015 =$B$12 LN(RAND()) =E10+F9 Andesti 110 --$B$12 LN(RAND()) =E11+F10 CRARANDA --$B$12*LN(RAND()) =E12+F11 NANO/ -12-17 -513-11 =$B$12 LN (RAND()) =E13+F12 SDC12N/BAND/-5144512 =$B$12 LN (RAND()) =E14+F13 RAVDAND/-515-514 =$B$12*LN(RAND()) =E15+F14 $B$121NWAND/11 -5164515 =$B$12 LN(RAND()) =E16+F15 $B$12*IN/RANDO) 17+16 =-$B$12*LN(RAND()) =E17+F16 GREINBAND/11 -519-517 =$B$12*LN(RAND()) =E18+F17 -17719.922 17710022 GREINIRANOX-519-518 --$B$12 LN(RAND()) =E19+F18 -CR611NBAND/1-5204519 -$B$12 LN(RAND()) www -18701.296 Setti -18479.250 com -18970.637 www.wor -18936.447 00 17 17 -17970.001 18 -19753.951 19 -18016.249 20 -18310.843 21 -17796.992 22 -17927 420 15 13 -18395.900 1.com -18533.688 10174934 -18174.934 19726 590 -18236.589 19062 063 -18063.082 -17170.022 Sample 2 Number SKS28 3 4 5 6 7 8 16 14 17 15 1 -18097.076 2 -18074.074 43240 3 -18164.035 4 -18267.836 5 -18148.436 -17919.622 7500 458 24 550 0.24 29732 25000 -811/24 Results of Monte Carlo Simulation Sample Number SHS28 6 7 -18270.916 8 -19454.259 9 -17976.283 10 -18287.870 11 -18229.493 12 -18083.780 =NORMINV(RAND().8.5%,1%) -B10-MAX(0,E3-30000)*89 -18284.855 -18067.705 -18110.372 18 16 -18192.008 19 17 -17966.616 20 18 -18261.984 21 19 -18129.281 22 20 -18169.362 23 21 -18947.639 24 22 -18438.251 25 22 18700 650 30000 F Cumulative Mileage G Cashflow =-82 C Month 0 1 2 E 3 4 5 6 7 8 D 9 10 11 12 13 14 15 16 17 18 19 20 21 Mileage =$B$12 LN (RAND()) =E3 =$B$12 LN (RAND()) =E4+F3 --$B$12 LN(RAND()) =ES+F4 =$B$12 LN (RAND()) =E6+F5 =-$B$12 LN (RAND()) =E7+F6 --$B$12 LN (RAND()) =EB+F7 =-$B$12 LN (RAND()) =E9+F8 --$B$12 LN(RAND()) =E10+F9 -$B$12 LN(RAND()) -E11+F10 =$B$12 LN (RAND()) =E12+F11 =$B$12 LN (RAND()) =E13+F12 $B$12 LN(RAND()) -E14+F13 --$B$12 LN(RAND()) =E15+F14 =-$B$12 LN(RAND()) =E16+F15 =-$B$12 LN (RAND()) =E17+F16 =$B$12 LN (RAND()) =E18+F17 =-$B$12 LN(RAND()) =E19+F18 =$B$12 LN (RAND()) =E20+F19 =-$B$12 LN(RAND()) =E21+F20 =-$B$12 LN(RAND()) =E22+F21 --$B$12 LN (RAND()) =E23+F22 =$B$12 LN(RAND()) =E24+F23 H Purchase A B C D Results of Monte Carlo Simulation 14607 64/160614/12/012 =G12/(1+$B$14/12)^SD12 -622/(1+$B$14/12)^$022 =G22/(1+$B$14/12)^$D22 -623/(1+$B$14/12)^5D23 -G23/(1+58514/12)^SD23 =G24/(1+$B$14/12)^$D24 -G25/(1+$B$14/12)^$D25 =G26/(1+$B$14/12)^$D26 -B10-MAX(C-G27/(1+$B$14/12)^$D27 NPV = =SUM(H3:H27) DCF =G3/(1+$B$14/12)^$D3 -G4/(1+$B$14/12)^SD4 =G5/(1+$B$14/12)^$D5 -G6/(1+$B$14/12)^SD6 =G7/(1+$B$14/12)^$D7 -G9/11+59514/121658 -G8/(1+$B$14/12)^SD8 -69/11-60611/12) -G9/(1+$B$14/12)^$D9 tot i otic vic $8$6-IF(F9>$B$16, F9-MAX(F8,$B$16),0) "$B$9 CDCE JEJC10NCDC16 E10.MAVIER SOSIS) IRSO =G10/(1+$B$14/12)^$D10 --$B$6-IF(F10>=$BS16,F10-MAX(F9,$B$16),0) *$B$9 -630/60634/160 633/11-60614/1214601 =G11/(1+$B$14/12)^$D11 6066 IE/ESDC1G E11 MAYISO SRCIC) OLTER $8$6-IF(F11-$B$16,F11-MAX(F10,S$B$16).0)*$B$9 CDC JE/13-0616 E12 MAXIEST CR6161 16 -$B$6-IF(F12=SBS16,F12-MAX(F11,$B$16),0)*$B$9 33/36064/1216012 CDCE JE/E12R16 E12 MAYIE12 6616) CpCo -G13/(1+$B$14/12)^$D13 --$B$6-IF(F13-$B$16,F13-MAX(F12,$B$16),0)*$B$9 Simulation Stats 1000 repetitions 8 seconds Cumulative Mileage Cashflow G ali.6063/116014 =G14/(1+$B$14/12)^$D14 -615/(1+$B$14/121015 -G15/(1+$B$14/12) "$D15 6/1603A/1216016 -G16/(1+$B$14/12)^$D16 -617/11-60514/12/2017 -G17/(1+$B$14/12)^$017 CDC6.JE/E14CD616 ETA-MAXIE12 CD616) 01+COCO $B$6-IF(F14>=$B$16,F14-MAX(F13,$B$16),0)*$B$9. CDSG JELE15-CR$16 515 MAVI1A SP16) IXSp.co -$8$6-IF(F15>-$BS16,F15-MAX(F14,$B$16).0)*$B$9 CDC6.JE/E16-CR$16 516 MAYICIS CR161 1+CPco $B$6-IF(F16>=$B$16,F16-MAX(F15,$B$16),0)*$B$9 6066.JE/E17-60816 17.MAVIE16 606161 16 $B$6-IF(F17>=$B$16,F17-MAX(F16,$B$16).0)*$B$9 19/11160614/12/19 CDC615/519-60616 512.MAY/E17 SP161 160 =G18/(1+$B$14/12)^$D18 --$8$6-IF(F18>=$B$16,F18-MAX(F17,$B$16),0)*$B$9 -619/11460614/12146019 -G19/(1+$B$14/12)^$D19 -620/11-58514/12 =G20/(1+$B$14/12)^$D20 -621/(1+$B$14/12)^021 -G21/(1+$B$14/12)^$D21 Simulation Stats =-82 C -21400 1000 repetitions 7 seconds G =-(85+88) --SBS6-IF(F4> SBS16,F4-MAX(F3.$B$16),0) $B$9 =-$8$6-IF(F5>$B$16,F5-MAX(F4,$B$16),0)*$B$9 -$B$6-IF(F6>=$B$16,F6-MAX(F5,$B$16),0)*$B$9 -$B$6-IF(F7>-$B$16,F7-MAX(F6,$B$16),0)*$B$9 $B$6-IF(F8>=$B$16,F8-MAX(F7,$B$16),0)"$B$9 Summary Statistics Average -18299.008 SD Max Min 638.7330 -16128.007 -21309.469 H CD66-1/10-6S16 519-MAYIE19 GR$161 1650 SBS6-IF(F19>=SBS16,F19-MAX(F18,$B$16).0) *$B$9 -CR$6.15/20-p$16 520 MAYIE10 606161 160o -$B$6-IF(F20-$B$16,F20-MAX(F19,$B$16),0)*$B$9 SBS6-JE/F21>-SBS16 F21-MAY(520 $B$16) *SBSO --$B$6-IF(F21>-SBS16,F21-MAX(F20,$B$16),0) *$B$9 Purchase =SBS6-IF(F22>=$B$16.F22.MAX(E21 $B$16101*$B$9 -$B$6-IF(F22>=$B$16,F22-MAX(F21,$B$16),0) *$B$9 SBS6-IF(F23>=$B$16,F23-MAX(F22,SB$16).0)*$B$9 -$8$6-IF(F24>=$B$16,F24-MAX(F23,$B$16),0) *$B$9 --$B$6-IF(F25>$B$16,F25-MAX(F24,$B$16).0)*$B$9 --$B$6-IF(F26>=$B$16,F26-MAX(F25,$B$16),0)*$B$9 =-$B$6-IF(F27>=$B$16,F27-MAX(F26,$B$16),0) *$B$9 -20400 DCF =G3/(1+$B$14/12)^$D3 =G4/(1+$B$14/12)^$D4 --$B$6-IF(F4>=$B$16,F4-MAX(F3,$B$16).0) *$B$9 -G5/(1+$B$14/12)^$05 --$B$6-IF(F5>-$B$16,F5-MAX(F4,$B$16).0) "$B$9 -G6/(1+$B$14/12)^$D6 --$B$6-IF(F6>=$B$16,F6-MAX(F5, $B$16),0) "$B$9 -G7/(1+$B$14/12)^$D7 --$B$6-IF(F7>=$B$16,F7-MAX(F6, $B$16).0)*$B$9 -G8/(1+$B$14/12) 08 --$B$6-IF(F8>=$B$16,F8-MAX(F7,$B$16),0) "$B$9 -G9/(1+$B$14/12)^$D9 --$B$6-IF(F9>=$B$16, F9-MAX(F8,$B$16),0) *$B$9 -G10/(1+$B$14/12)^$D10 --$B$6-IF(F10>-$B$16,F10-MAX(F9,$B$16),0) *$B$S -G11/(1+$B$14/12)^$D11 --$B$6-IF(F11>=$B$16,F11-MAX(F10,$B$16).0) $BS -G12/(1+$B$14/12)^$D12 =-$B$6-IF(F12>=$B$16,F12-MAX(F11,$B$16),0)*$B$ -G13/(1+$B$14/12)^$D13 =-$B$6-IF(F13>=$B$16,F13-MAX(F12,$B$16),0) $B$ -G14/(1+$B$14/12)^$D14 --$B$6-IF(F14>=$B$16,F14-MAX(F13,$B$16).0) $B$ -G15/(1+$B$14/12)^$D15 -$B$6-IF(F15>=$B$16,F15-MAX(F14,$B$16),0) $B$ =G16/(1+$B$14/12)^$D16 --$B$6-IF(F16-$B$16,F16-MAX(F15,$B$16),0) $B$ -G17/(1+$B$14/12)^$D17 --$B$6-IF(F17>-$B$16,F17-MAX(F16,$B$16),0)*$B$ -G18/(1+$B$14/12)^$D18 --$B$6-IF(F18-$B$16, F18-MAX(F17,$B$16),0)*$B$ -G19/(1+$B$14/12)^$D19 =$B$6-IF(F19 $B$16,F19-MAX(F18,$B$16),0) $B$ -G20/(1+$B$14/12)^$D20 --$B$6-IF(F20-$B$16,F20-MAX(F19,$B$16),0) $B$ =G21/(1+$B$14/12)^$D21 =$B$6-IF(F21>=$B$16,F21-MAX(F20,$B$16),0) $BS -G22/(1+$B$14/12)^$D22 $B$6-IF(F22>=$B$16,F22-MAX(F21,$B$16),0) $B$ -G23/(1+$B$14/12)^$D23 =-$B$6-IF(F23>=$B$16,F23-MAX(F22,$B$16),0)*$B$ -G24/(1+$B$14/12)^$D24 --$B$6-IF(F24>=SBS16,F24-MAX(F23,$B$16),0) $B$ Cashflow 1 -22000 Lease NPV = Histogram of $H$28 -19400 J -18400 Summary Statistics. Average -18273.093 SD Max 398.4021 -17825.588 -21949.809 Min Notes -21000 -17400 =-(85+88) -20000 L DCF =J3/(1+$B$14/12)^$D3 =J4/(1+$B$14/12)^SD4 =J5/(1+$B$14/12)^$D5 -J6/(1+$B$14/12)^$D6 =J7/(1+$B$14/12)^$D7 10/11160614/11 =J8/(1+$B$14/12)^$DB -10/11260614/421 =19/(1+$B$14/12)^$D9 -110/11+514/12/2016 =J10/(1+$B$14/12)^SD10 -111/(1+68514/12) =J11/(1+$B$14/12)^$D11 152/11160614/201 =J12/(1+$B$14/12)^$D12 -112/11-60514/12146012 =J13/(1+$B$14/12)^$D13 -114/11+50514/12165014 =J14/(1+$B$14/12)^$D14 -115/11+$B$1/A/12015 =J15/(1+$B$14/12)^$D15 -116/11+$B$14/12165016 =J16/(1+$B$14/12)^$D16 -117//1460514/12/17 =J17/(1+$B$14/12)^SD17 -119//1+59514/121/5018 =J18/(1+$B$14/12)^$D18 -119//1+59514/12)65019 =J19/(1+$B$14/12)^$D19 -120/11+59514/121020 =J20/(1+$B$14/12)^$D20 -121//1+R$14/12145021 =J21/(1+$B$14/12)^$D21 -122/(1+$B$14/12165022 =122/(1+$B$14/12)^$D22 -123/(1+$B$14/12)^SD23 -124/(1+$B$14/12)^$D24 -125/(1+$8$14/12)^SD25 -J26/(1+$B$14/12)^$D26 -J27/(1+$B$14/12)^SD27 =SUM(K3:K27) N -16400 Histogram of $K$28 Cashflow 0 Notes -19000 M N Lease -18000 Q O

Answer rating: 100% (QA)

Here are the key steps to generate and interpret the results of a Monte Carlo simulation 1 Define th... View the full answer