Follow the instruction to do a part of group assignment

Business Finance

Abdill Career College

Question Description

Follow the instruction to do a part of group assignment


Please note you will only need to do the part of input


Please go through all instruction very carefully


The excel is required


All the work must be original


Turnitin report is required

Unformatted Attachment Preview

ACST2001 Financial Modelling Spreadsheet Project Task 3 S1 2020 In SPT3 we extend your modelling from SPT1 to income streams. Specifically, we generate the retirement income stream lost by withdrawing a lump sum (now) from your super fund. In addition, given an indivdual’s desired income stream at retirement, we generate the (lump sum) investment needed now to realise that goal. Your modelling needs knowledge of interest rates applicable from today onwards. For simplicity, assume this rate is the Australian 10-year government bond yield as at the end of 2019 (31.xii.19), plus 100 basis points (i.e., a j2 rate). You can use FactSet to find this value. A user of your spreadsheet will need to input the following. a. Age (an integer) b. Gender (‘M’ or ‘F’) c. Desired superannuation withdrawal amount (in dollars) d. Desired annual income for ages 65 to 85, inclusive (males), or 65 to 87, inclusive (females). Assume this annual amount is paid on each birthday, starting at age 65, and ending at 85 for males and 87 for females. This stream of annual payments is what we refer to below as the user’s retirement income stream. The spreadsheet will then generate the following outputs. a. The reduction in the user’s retirement income stream by making the above withdrawal from the user’s superannuation fund. For a male user, this retirement income stream refers to constant annual payments made at ages 65 to 85, inclusive. For a female user, this retirement income stream refers to constant annual payments made from ages 65 to 87, inclusive. b. The lump sum required (today) at the user’s current (integer) age to generate the desired annual retirement income stream inputted. c. A bar chart consisting of one bar representing the reduction in the user’s annual retirement income stream (generated by the user’s current superannuation withdrawal) and another bar representing the user’s desired annual retirement income stream. Test your spreadsheet model with a variety of different inputs and check its outputs by manual calculations to make sure your model works correctly. End of task 1 Please turn over ACST2001 Financial Modelling Spreadsheet Project Task 3 S1 2020 The submission deadline for spreadsheet project task 3 is 11.30 p.m. on 04 May 2020. You need to submit your solutions (in one functional .xlsx or .xls file) to the link on iLearn prior to this time. Your spreadsheet should be be clearly labelled and easy to understand. Make sure you identify what the inputs and outputs are. Include necessary information (e.g., title, axis titles, etc.) in your plot. Document and describe the steps in the development of each tab of your spreadsheet. Please note that uploading a file can take up to 15 minutes. You need to submit your file at least 20 minutes before the deadline to ensure a successful submission. 2 Input Date of purchase Date of Maturity Coupon rate Yield Face Value Maturity Value Time periods Output Half-Yearly coupon Half-Yearly Yield Purchase Price 2020 15-Sep 2026 15-Sep 6% Per annum 7% Per annum $ 100,00 $ 100,00 12 $ 3,00 3,50% $95,17 Description: The input data should having date of purchase and maturity, coupon rate, yield to maturity, face value and maturity value ready In the output section, the Hald-Yearly coupon and Yield section will automatically indicate the date output. The Purchase price will be indicated using Excel PV Function. (Assuming the coupons are paid half-yearly) Input Date of purchase Date of Maturity Coupon rate Tax on Coupon Yield Face Value Maturity Value Time periods Output Half-Yearly yield Half-yearly coupon Tax on coupon Net Half-Yearly Coupon Purchase price 2020 15-Sep 2026 15-Sep 6% Per annum 30% 7% Per annum $ 100,00 $ 100,00 12 $ $ $ 3,50% 3,00 0,90 2,10 $86,47 Description: The input data should having date of purchase and maturity, coupon rate and tax rate on coupon, yield to maturity, face value and maturity value ready In the output section, the Hald-Yearly coupon and Yield section will automatically indicate the date output. The net Half-Yearly coupon is indicated by deducting tax on coupon from half-yearly coupon. The Purchase price will be indicated using Excel PV Function. (Assuming the coupons are paid half-yearly) Input Date of purchase Date of Maturity Coupon rate Tax on Coupon Yield Face Value Maturity Value Tax on Capital Gain Time periods 2020 15-Sep 2026 15-Sep 6% Per annum 30% 7% Per annum $ 100,000 $ 100,000 30% 12 Output Half-Yearly yield Half-yearly coupon Tax on coupon Net Half-Yearly Coupon Purchase Price 3,50% $ 3,000 $ 0,900 $ 2,100 $ 84,103 Description: The input data should having date of purchase and maturity, coupon rate and tax rate on coupon, yield to maturity, face value and maturity value ready In the output section, the Hald-Yearly coupon and Yield section will automatically indicate the date output. The net Half-Yearly coupon is indicated by deducting tax on coupon from half-yearly coupon. The Purchase price will be indicated using Excel PV Function. But it needs to be noticed that the present value of all the deferred taxes on coupon and Capital Gains should be deducted from the original price Input Date of purchase Date of Maturity Coupon rate Tax on Coupon Yield Face Value Maturity Value Tax on Capital Gain Expected Price Time periods 2020 15-Sep 2026 15-Sep 6,000% Per annum 30% 4,667% Per annum $ 100,000 $ 100,000 30% $ 97,447 12 Output Half-Yearly yield Half-yearly coupon Tax on coupon Net Half-Yearly Coupon Capital Gain Tax on Capital Gain Purchase price 2,33% $ 3,000 $ 0,900 $ 2,100 $ 2,5530 $ 0,7659 $ 97,447 Description: The input data should having date of purchase and maturity, coupon rate and tax rate on coupon, yield to maturity (but leave it blank at this stage), face value and maturity value and tax on capital gain, and the expeted price ready In the output section, the Hald-Yearly coupon and Yield section will automatically indicate the date output. The net Half-Yearly coupon is indicated by deducting tax on coupon from half-yearly coupon. The Purchase price will be indicated using Excel PV Function. But it needs to be noticed that the present value of all the deferred taxes on coupon and Capital Gains should be deducted from the original price Finally we set the value of purchase price to 97.447, by changing the cell of Yield, Press Enter (Goal Seek Function) ...
Student has agreed that all tutoring, explanations, and answers provided by the tutor will be used to help in the learning process and in accordance with Studypool's honor code & terms of service.

This question has not been answered.

Create a free account to get help with this and any other question!