Data Window 100% GO *** Fri 9:07 pm Q Excel File Edit View Insert Format Tools AutoSave HESU- OFF Help FINC Assign 1 ashriya .xls - Compatibility Mode Tell me Q Home Insert Draw Page Layout Formulas Data Review View Share a Comments X LE ' ab Wrap Text VT D ov 3 V Paste ar Av 3 & Merge & Centre $ v % .00 0 Insert Delete V Format Ideas Conditional Format Formatting as Table Cell Styles Sort & Filter Find & Select D5 4 x fx 12094674 AB D E F G 15 1 2 16 17 18 3 B D 19 5 20 6 21 7 8 22 23 9 24 10 11 25 26 27 28 12 66,600.00 1.00 4.00 640,000.00 3.30 22,000.00 390.00 2,847.00 2.60 7.70 10.30 15.00 15.00 70.00 2.70 4.10 6.80 15.00 25.00 60.00 92.00 2.00 7.30 71,461.80 K L M N 13 14 29 15 16 17 18 19 30 31 32 33 34 35 36 37 38 20 P Q R S T U V W X 21 22 23 0 A Student Data Set Question 1-3, 4.2 & 5 Question 4.1 + Ready + 164% OCT 2 W P 21 Excel File Edit View Insert Format Tools Data Window Help 100% GO *** Fri 9:07 pm Q AutoSave OFF HESU- FINC Assign 1 ashriya .xls - Compatibility Mode Q Home Insert Draw Page Layout Formulas Data Review View Tell me Share 0 Comments X LE Ai ab Wrap Text 47. Ou Paste B IU ar Av 3 Merge & Centre $ % Loo Insert Delete Format Conditional Format Formatting as Table Cell Styles Sort & Filter Ideas Find & Select C21 fx A B D E F G H 1 J 1.11 Agnus deposits an inheritance of $[A] into a bank account that earns [B]% pa interest, calculated annually. What will her balance be in [C] years if the interest compounds? $ 67,268.50 1 FP(1+r)nt. = 66,600*1.01=67,268. A 66,600.00 3 1.12 In the question above, what will her balance be if the interest is calculated monthly? $ 1,401.43 1 67268/4 = 16817/12=$1401.43 B 1.00 4 In the question above, what will her balance be if the interest is calculated daily 1.13 (assume 365.25 days in a year)? $ 3.84 1 1401.43/365.25 c 4.00 5 Matthew buys a house for $[D] with the intention of owning it for [C] years. Historically 1.2 house prices in the area have risen by [E]%pa. What will the value of his house be at when he comes to sell it? $ 728,754.51 1 FV=Pv(1+r)n =640,00(1+0.033) D 640,000.00 6 1.3 John has just turned 21. His father gave him a birthday present of $[F] which represents the compounded principal of a term deposit that his father had set up on the day of his birth. If the term deposit had an interest rate of [B]% over that time compounded monthly, what was the amount his father originally deposited? $ 17,847.43 1 FVn=CF ((1+i/m)]^(nxm)-1)/(i/m) E 3.30 7 Anthony has just retired. He has an investment property that he bought 30 years ago for 1.4 $[F]. It is now worth $[D]. What rate of property growth has the property enjoyed over that time? r=0.118 = 11.8% 1 Dn or, 640,000= 22,000 (1+r)^30 = 0. F 22,000.00 8 1.5 Methuselah has decided to sell some shares in a company that he bought when he was a young man. The parcel of shares is now worth $[A]. He bought them for $[F]. How long ago (in years and decimals of years) did he buy them if share prices have been rising at [B]% pa? 2.97 years 1 Fv=Pv(1+r)n G 390.00 9 A Student Data Set Question 1-3, 4.2 & 5 Question 4.1 + Ready 146% 2 W A P 100% GO *** Fri 9:07 pm Q Excel File Edit View Insert Format Tools AutoSave OFF HESU- Data Window Help FINC Assign 1 ashriya .xls - Compatibility Mode Review View Tell me Q Home Insert Draw Page Layout Formulas Data Share 0 Comments X LE ' ab Wrap Text 47. Ou 5 V Paste a. Av .00 3 & Merge & Centre $ % % V Delete Insert Format Ideas Conditional Format Formatting as Table Cell Styles Sort & Filter Find & Select C21 4 x fx A B D E F G H 1 J 2.1 Mary has just inherited a parcel of shares. She anticipates an annual dividend of $[G]. She is thinking of selling the shares in 10 years' time and estimates that they will be worth $[F] by then. If comparable investments are returning (E]% pa, what is the value of her inheritance in today's dollars? $ 281.88 1 281.88 . 2,847.00 10 Martha has just changed jobs and has received a termination payment of $[F] from her old employer. She has decided to invest it into a retirement scheme that promises to 2.2 pay [E]% pa interest, compounded monthly. She is also committing herself to pay $[G] per month into the scheme at the end of each month till she retires in forty years' time. What will her retirement investment be worth then? $ 82,206.00 1 82,206.20 I 2.60 11 3.1 Cecelia bought an investment a year ago for $[A], which is now worth $[X]. If she also received a dividend of $[G], what is her holding period return? 7.88% 1 KP_1-Po)+CF)/Po = (71461-66600)+ J 7.70 12 Andrew is considering an investment that he estimates may provide returns of [1], [J], or 3.2 [K]% depending on the future market conditions. He estimates the probabilities of those outcomes as [L], [M], & [N]% respectively. What is his expected probably return? 0.09% 1 0.09 K 10.30 13 14 0.07484000 1 L -0.07484 0.273569004 15.00 15.00 15 0.27% 1 M 3.3 What is the variance of the returns in the investment Andrew is considering? 3.4 What is the standard deviation of the investment returns Andrew is considering? Elizabeth is considering buying a diversified portfolio of investments with expected 3.5 returns of [0], [P] & [Q]. She is considering buying in the proportions of [R], [S] & [T] respectively. What is his expected portfolio return. 5.51% 1 5.51 N 70.00 16 rance.insensidacine. 10. Lond...L . fac.al. -100..bial A Student Data Set Question 1-3, 4.2 & 5 Question 4.1 + Ready + 146% OCT 2 W 21 100% GO *** Fri 9:08 pm Q Excel File Edit View Insert Format Tools AutoSave BESU OFF Data Window Help D. FINC Assign 1 ashriya .xls - Compatibility Mode Review View Tell me Q Home Insert Draw Page Layout Formulas Data Share a Comments X LE ' ab Wrap Text 47. Ou 5 V Paste .00 ar Av 3 & Merge & Centre V $ % Delete Insert Format Ideas Conditional Format Formatting as Table Cell Styles Sort & Filter Find & Select C21 fox D E F G H 1 J TO 1 1 A B respectively. VTOL I TIJ Capccicu po CIUITV TELUIT. Frances is considering a 10 year bond with a face value of $100 which pays an annual 4.2 coupon payment of $[B]. What price should she pay for it if comparable investments return [E]%? 4.3 If she can buy the bond for $[U] what would be the yield to maturity? $ 28.41 2 1/(1+i/m)^(mxn) = (0.0383/1.383)*30 o 2.70 17 18 0.10% 2 (10.48) P 4.10 5.1 If the risk free rate is [B]% and the return on the All Ordinaries basket of equities shares is [W]%, what will be the expected return of a share with a beta of 1.3? 10.49% 1 In= Risk Free rate + (Beta market re Q 6.80 19 An ordinary share is expected to pay a dividend of $[C] during the next twelve months. 5.2 The required rate of return is [W]% and the anticipated growth is[V]%. What is its expected price? $ 75.47 1 D/(R-g) = 4/(7.3-2) = 0.754 R 15.00 20 5.3 In the example above, further investigation reveals that the constant dividend pattern will not actually emerge until four years' time. The dividend expected in the next year is only expected to be 20% of the anticipated eventual constant dividend ($[C]). The dividend the following year will be 50% of the anticipated eventual constant dividend ($[C]). The dividend in the third year from new will be 80% of the anticipated eventual constant dividend ($[C]). What would be the expected price under these conditions? 3 S 25.00 21 22 T T U V W 60.00 92.00 2.00 23 24 25 26 7.30 71,461.80 A Student Data Set Question 1-3, 4.2 & 5 & 5 Question 4.1 + Ready + 146% OCT 2 PAOL W P 2 BU Excel File Edit View Insert Format Tools Data Window Help 100% GO *** Fri 9:08 pm Q AutoSave OFF HESU- FINC Assign 1 ashriya .xls - Compatibility Mode Q Home Insert Draw Page Layout Formulas Data Review View Tell me Share a Comments X LE " ab Wrap Text ov Paste B I U ar Av $ % == 3 Merge & Centre Insert Delete Ideas Format Conditional Format Formatting as Table Cell Styles Sort & Filter Find & Select D256 fx B D E F G H 1 J K M 1 2 Part Question Answer Marks Christopher has taken a mortgage loan of $[A] over 30 year period at an interest rate of [E]%pa compounded monthly. Prepare an amortisation schedule to find his outstanding principal at the end of the 240th month. 3 4.11 4 $ 10,728.37 1 5 4.12 What is his monthly loan repament Enter his outstanding principal at the end of the 240th month >>>>> What is his effective interest rate? $ 55,871.63 4 6 4.13 3.35% 1 Data Value 7 8 B 9 C 10 D E 11 12 Amortisation Schedule: 1 5,565.26 2. 5,580.57 3 5,595.91 4. 5,611.30 5 5,626.73 6 5,642.21 7 5,657.72 F 66,600.00 1.00 4.00 640,000.00 3.30 22,000.00 390.00 2,847.00 2.60 7.70 61,034.74 61,019.43 61,004.09 60,988.70 60,973.27 60,957.79 60,942.28 13 0.033 0.03327225 0.024954749 0.033410626 0.033438484 0.033457091 0.033470399 G 14 H 1 15 16 J Student Data Set Question 1-3, 4.2 & 5 Question 4.1 Ready 186% 2 W P Data Window 100% GO *** Fri 9:07 pm Q Excel File Edit View Insert Format Tools AutoSave HESU- OFF Help FINC Assign 1 ashriya .xls - Compatibility Mode Tell me Q Home Insert Draw Page Layout Formulas Data Review View Share a Comments X LE ' ab Wrap Text VT D ov 3 V Paste ar Av 3 & Merge & Centre $ v % .00 0 Insert Delete V Format Ideas Conditional Format Formatting as Table Cell Styles Sort & Filter Find & Select D5 4 x fx 12094674 AB D E F G 15 1 2 16 17 18 3 B D 19 5 20 6 21 7 8 22 23 9 24 10 11 25 26 27 28 12 66,600.00 1.00 4.00 640,000.00 3.30 22,000.00 390.00 2,847.00 2.60 7.70 10.30 15.00 15.00 70.00 2.70 4.10 6.80 15.00 25.00 60.00 92.00 2.00 7.30 71,461.80 K L M N 13 14 29 15 16 17 18 19 30 31 32 33 34 35 36 37 38 20 P Q R S T U V W X 21 22 23 0 A Student Data Set Question 1-3, 4.2 & 5 Question 4.1 + Ready + 164% OCT 2 W P 21 Excel File Edit View Insert Format Tools Data Window Help 100% GO *** Fri 9:07 pm Q AutoSave OFF HESU- FINC Assign 1 ashriya .xls - Compatibility Mode Q Home Insert Draw Page Layout Formulas Data Review View Tell me Share 0 Comments X LE Ai ab Wrap Text 47. Ou Paste B IU ar Av 3 Merge & Centre $ % Loo Insert Delete Format Conditional Format Formatting as Table Cell Styles Sort & Filter Ideas Find & Select C21 fx A B D E F G H 1 J 1.11 Agnus deposits an inheritance of $[A] into a bank account that earns [B]% pa interest, calculated annually. What will her balance be in [C] years if the interest compounds? $ 67,268.50 1 FP(1+r)nt. = 66,600*1.01=67,268. A 66,600.00 3 1.12 In the question above, what will her balance be if the interest is calculated monthly? $ 1,401.43 1 67268/4 = 16817/12=$1401.43 B 1.00 4 In the question above, what will her balance be if the interest is calculated daily 1.13 (assume 365.25 days in a year)? $ 3.84 1 1401.43/365.25 c 4.00 5 Matthew buys a house for $[D] with the intention of owning it for [C] years. Historically 1.2 house prices in the area have risen by [E]%pa. What will the value of his house be at when he comes to sell it? $ 728,754.51 1 FV=Pv(1+r)n =640,00(1+0.033) D 640,000.00 6 1.3 John has just turned 21. His father gave him a birthday present of $[F] which represents the compounded principal of a term deposit that his father had set up on the day of his birth. If the term deposit had an interest rate of [B]% over that time compounded monthly, what was the amount his father originally deposited? $ 17,847.43 1 FVn=CF ((1+i/m)]^(nxm)-1)/(i/m) E 3.30 7 Anthony has just retired. He has an investment property that he bought 30 years ago for 1.4 $[F]. It is now worth $[D]. What rate of property growth has the property enjoyed over that time? r=0.118 = 11.8% 1 Dn or, 640,000= 22,000 (1+r)^30 = 0. F 22,000.00 8 1.5 Methuselah has decided to sell some shares in a company that he bought when he was a young man. The parcel of shares is now worth $[A]. He bought them for $[F]. How long ago (in years and decimals of years) did he buy them if share prices have been rising at [B]% pa? 2.97 years 1 Fv=Pv(1+r)n G 390.00 9 A Student Data Set Question 1-3, 4.2 & 5 Question 4.1 + Ready 146% 2 W A P 100% GO *** Fri 9:07 pm Q Excel File Edit View Insert Format Tools AutoSave OFF HESU- Data Window Help FINC Assign 1 ashriya .xls - Compatibility Mode Review View Tell me Q Home Insert Draw Page Layout Formulas Data Share 0 Comments X LE ' ab Wrap Text 47. Ou 5 V Paste a. Av .00 3 & Merge & Centre $ % % V Delete Insert Format Ideas Conditional Format Formatting as Table Cell Styles Sort & Filter Find & Select C21 4 x fx A B D E F G H 1 J 2.1 Mary has just inherited a parcel of shares. She anticipates an annual dividend of $[G]. She is thinking of selling the shares in 10 years' time and estimates that they will be worth $[F] by then. If comparable investments are returning (E]% pa, what is the value of her inheritance in today's dollars? $ 281.88 1 281.88 . 2,847.00 10 Martha has just changed jobs and has received a termination payment of $[F] from her old employer. She has decided to invest it into a retirement scheme that promises to 2.2 pay [E]% pa interest, compounded monthly. She is also committing herself to pay $[G] per month into the scheme at the end of each month till she retires in forty years' time. What will her retirement investment be worth then? $ 82,206.00 1 82,206.20 I 2.60 11 3.1 Cecelia bought an investment a year ago for $[A], which is now worth $[X]. If she also received a dividend of $[G], what is her holding period return? 7.88% 1 KP_1-Po)+CF)/Po = (71461-66600)+ J 7.70 12 Andrew is considering an investment that he estimates may provide returns of [1], [J], or 3.2 [K]% depending on the future market conditions. He estimates the probabilities of those outcomes as [L], [M], & [N]% respectively. What is his expected probably return? 0.09% 1 0.09 K 10.30 13 14 0.07484000 1 L -0.07484 0.273569004 15.00 15.00 15 0.27% 1 M 3.3 What is the variance of the returns in the investment Andrew is considering? 3.4 What is the standard deviation of the investment returns Andrew is considering? Elizabeth is considering buying a diversified portfolio of investments with expected 3.5 returns of [0], [P] & [Q]. She is considering buying in the proportions of [R], [S] & [T] respectively. What is his expected portfolio return. 5.51% 1 5.51 N 70.00 16 rance.insensidacine. 10. Lond...L . fac.al. -100..bial A Student Data Set Question 1-3, 4.2 & 5 Question 4.1 + Ready + 146% OCT 2 W 21 100% GO *** Fri 9:08 pm Q Excel File Edit View Insert Format Tools AutoSave BESU OFF Data Window Help D. FINC Assign 1 ashriya .xls - Compatibility Mode Review View Tell me Q Home Insert Draw Page Layout Formulas Data Share a Comments X LE ' ab Wrap Text 47. Ou 5 V Paste .00 ar Av 3 & Merge & Centre V $ % Delete Insert Format Ideas Conditional Format Formatting as Table Cell Styles Sort & Filter Find & Select C21 fox D E F G H 1 J TO 1 1 A B respectively. VTOL I TIJ Capccicu po CIUITV TELUIT. Frances is considering a 10 year bond with a face value of $100 which pays an annual 4.2 coupon payment of $[B]. What price should she pay for it if comparable investments return [E]%? 4.3 If she can buy the bond for $[U] what would be the yield to maturity? $ 28.41 2 1/(1+i/m)^(mxn) = (0.0383/1.383)*30 o 2.70 17 18 0.10% 2 (10.48) P 4.10 5.1 If the risk free rate is [B]% and the return on the All Ordinaries basket of equities shares is [W]%, what will be the expected return of a share with a beta of 1.3? 10.49% 1 In= Risk Free rate + (Beta market re Q 6.80 19 An ordinary share is expected to pay a dividend of $[C] during the next twelve months. 5.2 The required rate of return is [W]% and the anticipated growth is[V]%. What is its expected price? $ 75.47 1 D/(R-g) = 4/(7.3-2) = 0.754 R 15.00 20 5.3 In the example above, further investigation reveals that the constant dividend pattern will not actually emerge until four years' time. The dividend expected in the next year is only expected to be 20% of the anticipated eventual constant dividend ($[C]). The dividend the following year will be 50% of the anticipated eventual constant dividend ($[C]). The dividend in the third year from new will be 80% of the anticipated eventual constant dividend ($[C]). What would be the expected price under these conditions? 3 S 25.00 21 22 T T U V W 60.00 92.00 2.00 23 24 25 26 7.30 71,461.80 A Student Data Set Question 1-3, 4.2 & 5 & 5 Question 4.1 + Ready + 146% OCT 2 PAOL W P 2 BU Excel File Edit View Insert Format Tools Data Window Help 100% GO *** Fri 9:08 pm Q AutoSave OFF HESU- FINC Assign 1 ashriya .xls - Compatibility Mode Q Home Insert Draw Page Layout Formulas Data Review View Tell me Share a Comments X LE " ab Wrap Text ov Paste B I U ar Av $ % == 3 Merge & Centre Insert Delete Ideas Format Conditional Format Formatting as Table Cell Styles Sort & Filter Find & Select D256 fx B D E F G H 1 J K M 1 2 Part Question Answer Marks Christopher has taken a mortgage loan of $[A] over 30 year period at an interest rate of [E]%pa compounded monthly. Prepare an amortisation schedule to find his outstanding principal at the end of the 240th month. 3 4.11 4 $ 10,728.37 1 5 4.12 What is his monthly loan repament Enter his outstanding principal at the end of the 240th month >>>>> What is his effective interest rate? $ 55,871.63 4 6 4.13 3.35% 1 Data Value 7 8 B 9 C 10 D E 11 12 Amortisation Schedule: 1 5,565.26 2. 5,580.57 3 5,595.91 4. 5,611.30 5 5,626.73 6 5,642.21 7 5,657.72 F 66,600.00 1.00 4.00 640,000.00 3.30 22,000.00 390.00 2,847.00 2.60 7.70 61,034.74 61,019.43 61,004.09 60,988.70 60,973.27 60,957.79 60,942.28 13 0.033 0.03327225 0.024954749 0.033410626 0.033438484 0.033457091 0.033470399 G 14 H 1 15 16 J Student Data Set Question 1-3, 4.2 & 5 Question 4.1 Ready 186% 2 W P