The writer is very fast, professional and responded to the review request fast also. Thank you.
Presentation/PPT
27/08/2022
Today is 1 January 2018. Judy is 20 years old today and she is planning to purchase a car priced at $80,000 on 1 July 2022. Judy believes that, at the time of purchasing the car, she should have savings to cover 40% of the car price (i.e., $32,000) and she can borrow the remaining 60% of the car price (i.e., $48,000) through a 7-year loan.
To save the 40% of the car price (i.e., $32,000), Judy plans to deposit x% of her monthly after-tax salary into a fund at the end of each month from July 2020 to June 2022.
Judy assumes that she can successfully graduate from university and find a job on 1 July 2020. Judy estimates that her initial before-tax salary will be $60,000 p.a. payable monthly. All her salary will be paid at the end of each month. Judy forecasts that her salary will grow at the rate of 2% p.a. The salary adjustment will only be conducted at the beginning of July in each year. For example, it is assumed that her salary is $60,000 p.a. payable monthly from July 2020 to June 2021, and then her salary is $60, 000(1 + 2%) p.a. payable monthly from July 2021 to June 2022.
The deposit fund yield is estimated to be 5.5% p.a. payable monthly (i.e., j12 = 5.5% p.a.) from July 2020 to June 2022.
Assume that all her salary is taxable. Progressive marginal individual income tax rates are as shown in table 1 (A5:C10). Based on the projected income and tax rate from table 1, we can calculate the income tax for a financial year1 . Then we can calculate the tax instalment and after-tax salary for each income salary payment (for simplicity, we assume that there is no Medicare Levy.). For example, if a person’s annual income is $50,000. Her annual income tax is $3, 572 + ($50, 000 − $37, 000) × 0.325 = $7, 797. For each income payment, her monthly tax instalment is $7,797/12=$649.75 and after-tax monthly income is $50, 000/12−$649.75 = $3, 516.92.
Judy calculates her future income, income tax and after-tax income from July 2020 to June 2022. She then sets a formula in F8 and uses Goal Seek to find the value of x.
**1Note that a financial year is defined as a period starting on 1 July and ending on the next 30 June in Australia.
Table 1: Excel spreadsheet. ———:-— Judy’s initial $30, 000 Financial Before-tax Tax amount Tax monthly-_—Fund yield 2020—2021 $60, 000.00 $11,047.00rate 3‘12 Income 2020—2022 $61, 200.00 $11,437.00growth rate p.a.. Income Minimum 2020 202 1threshold tax payment J uly—2 021 J uly—2022J u.ne June 0 — $18, 200 0 After-tax $4,079.41? 34.146317income permonth $18, 200 —$37, 000 $37, 000 — 32.5% $3,572 Total $32,000.00$87, 000 accumulateddeposit $87, 000 — 37% $19,822 Deposit rate 30.75%$ 180, 000 X 10 Above 45% $54,2323 180, 000 a. [2 marks] Give the Excel cell formulae you would use to calculate the valueof cells F2 and F3. b. [2 mark] Give the Excel cell formulae you would use to calculate the value ofcells F6 and GS. c. [3 mark] Give the Excel cell formula you would use to calculate the value ofcell F8. (1. [4 marks] Describe the steps Judy used to generate her result for 33% in cellF9 using Goal Seek.
The writer is very fast, professional and responded to the review request fast also. Thank you.
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.
Read moreEach paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.
Read moreThanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.
Read moreYour email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.
Read moreBy sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.
Read more