Loading... Please wait...

Categories

Our Newsletter


ACST 201 estimates that the required tuition fee on 1/1/2022 is $80,000. Tom is planning to invest 5 annual deposits in an investment fund and the assumed return rates...

Price:
$17.99
SKU:
701541


Product Description

He estimates that the required tuition fee on 1/1/2022 is
$80,000. Tom is planning to invest 5 annual deposits in an investment fund and the
assumed return rates (net rates) of that fund are shown in table 1 (j1 rates). The
first deposit will be made today (i.e., 1/1/2017) and the last one will be made on
1/1/2021. The deposit amount is x% of Tom’s after-tax income at each deposit
time. (30 marks)
Table 1: Investment fund return rates
Year
2017
2018
2019
2020
2021 Return rates
4% p.a.
4.2% p.a.
4.7% p.a.
4.8% p.a.
4.5% p.a. Tom’s current before-tax income is $80,000 and he believes his before-tax income
will increase by 3% every year. Assume that all his income is taxable. The progressive marginal individual income tax rates are as shown in table 2.
Table 2: Individual income tax rates
Taxable income
0–$18,200
$18,200–$37,000
$37,000–$87,000
$87,000–$180,000
$180,000 and over Tax on this income
Nil
19c for each $1 over $18,200
$3,572 plus 32.5c for each $1 over $37,000
$19,822 plus 37c for each $1 over $87,000
$54,232 plus 45c for each $1 over $180,000 a. [6 marks] Input the investment fund return rates table and individual income
tax rates table in your spreadsheet. Use a line chart to plot the investment
fund return rates table. Label this sheet as “Part a”.
1 ACST201 Financial Modelling Group Spreadsheet Task 3 S1 2017 b. [15 marks] Use functions or formulae to calculate Tom’s after-tax income from
2017 to 2021. Your calculation results should automatically update if his
income or slary growth rate changes.. Label this sheet as “Part b”.
c. [9 marks] Use Goal Seek to find the value of x%. Express your answer as a
percentage and rounded to two decimal places. Your calculation should refer
to the input values (i.e., before-tax income amount, fund return rates, tax
rates, income growth rate, etc.). Label this sheet as “Part c”.
Present your answers to the above questions in a functional Excel spreadsheet
(it should be a xlsx or xls file), giving each solution on a separate sheet (labelled
Part a, Part b, and Part c). Your spreadsheet should be clearly labelled and easy
to understand. Make sure you identify what the inputs and outputs are. Please
include the necessary information, e.g., title, axis titles, etc. in your plot. Please
document your working steps.


Find Similar Products by Category


Write your own product review

Product Reviews

This product hasn't received any reviews yet. Be the first to review this product!


Add to Wish List

Click the button below to add the ACST 201 estimates that the required tuition fee on 1/1/2022 is $80,000. Tom is planning to invest 5 annual deposits in an investment fund and the assumed return rates... to your wish list.