c. [12 marks]i. Calculate Judy’s monthly loan repayment amount. Assume that the re-payment is paid...

Question

c. [12 marks]i. Calculate Judy’s monthly loan repayment amount. Assume that the re-payment is paid at the beginning of each month.ii. Calculate the accumulated deposit value, ifx= 35%.iii. Calculate Judy’s average annual tax rate for each financial year from July2020 to June 2028 (to calculate it, divide the total annual tax paymentby Judy’s annual salary income.). Use a bar/column chart to presentyour results.
	
Document Preview: ACST201 Financial Modelling Spreadsheet Project Task 3 S1 2018
Spreadsheet Project Task 3
Today is 1 January 2018. Judy is 20 years old today and she is planning to purchase
a real estate with the price of $800,000 on 1 July 2028. Judy believes that, at
the time of purchasing the house, she should have saving to cover the 20% of the
house price (i.e., $160,000) over the period from January 2018 to June 2028 and
she can borrow the remaining 80% of the house price (i.e., $640,000) through a 30-
year mortgage (it starts from 1 July 2028) from MQU Bank at an interest rate of
j = 6% p.a.
12
To save the 20% of the house price (i.e., $160,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 XXXXXXXXXXmarks).
• Judy assumes that she can successfully graduate from university and ?nd 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 y% p.a. The salary
adjustment will only be conducted at the beginning of February in each year.
For example, it is assumed that her salary is $60,000 p.a. payable monthly
from July 2020 to January 2021, and then her salary is $60, XXXXXXXXXXy%) p.a.
payable monthly from February 2021 to January 2022. Judy assumes the value
of y is same as the Australian CPI rate for 2017. You need to use FactSet to
?nd the Australian CPI rate for 2017.
• The deposit fund yield is estimated to be 5.5% p.a. payable monthly (i.e.,
j = 5.5%) from July 2020 to December 2022 and is estimated to be 6.5%
12
p.a. payable monthly (j = 6.5%) from January 2023 to June 2028.
12
• Assume that all her salary income is taxable. Progressive marginal individual
income tax rates are as shown in table 1. Based on the projected income
and tax rate from table 1, we can calculate the income tax for a ?nancial
1
year . Then we can calculate the tax...

Rochak · Accepted Answer

Input
	Real estate price 	800000
	Downpayment required 	20%
	Loan	80%
	Deposits	x%
	Annual Salary	60000
	Monthly Salary	5000
	Salary growth	1.90%
	July 2020 to December 2022
	Deposit yield (Annual)	5.50%
	Deposit yield (Monthly)	0.46%
	January 2023 to June 2028
	Deposit yield (Annual)	6.50%
	Deposit yield (Monthly)	0.54%
Answer a
	i. Before tax Income per month
	Month	Salary
	Jul-21	$   5,000.00
	Aug-21	$   5,000.00
	Sep-21	$   5,000.00
	Oct-21	$   5,000.00
	Nov-21	$   5,000.00
	Dec-21	$   5,000.00
	Jan-22	$   5,000.00
	Feb-22	$   5,095.00
	Mar-22	$   5,095.00
	Apr-22	$   5,095.00
	May-22	$   5,095.00
	Jun-22	$   5,095.00
	Jul-22	$   5,095.00
	Aug-22	$   5,095.00
	Sep-22	$   5,095.00
	Oct-22	$   5,095.00
	Nov-22	$   5,095.00
	Dec-22	$   5,095.00
	Jan-23	$   5,095.00
	Feb-23	$   5,191.80
	Mar-23	$   5,191.80
	Apr-23	$   5,191.80
	May-23	$   5,191.80
	Jun-23	$   5,191.80
	Jul-23	$   5,191.80
	Aug-23	$   5,191.80
	Sep-23	$   5,191.80
	Oct-23	$   5,191.80
	Nov-23	$   5,191.80
	Dec-23	$   5,191.80
	Jan-24	$   5,191.80
	Feb-24	$   5,290.45
	Mar-24	$   5,290.45
	Apr-24	$   5,290.45
	May-24	$   5,290.45
	Jun-24	$   5,290.45
	Jul-24	$   5,290.45
	Aug-24	$   5,290.45
	Sep-24	$   5,290.45
	Oct-24	$   5,290.45
	Nov-24	$   5,290.45
	Dec-24	$   5,290.45
	Jan-25	$   5,290.45
	Feb-25	$   5,390.97
	Mar-25	$   5,390.97
	Apr-25	$   5,390.97
	May-25	$   5,390.97
	Jun-25	$   5,390.97
	Jul-25	$   5,390.97
	Aug-25	$   5,390.97
	Sep-25	$   5,390.97
	Oct-25	$   5,390.97
	Nov-25	$   5,390.97
	Dec-25	$   5,390.97
	Jan-26	$   5,390.97
	Feb-26	$   5,493.40
	Mar-26	$   5,493.40
	Apr-26	$   5,493.40
	May-26	$   5,493.40
	Jun-26	$   5,493.40
	Jul-26	$   5,493.40
	Aug-26	$   5,493.40
	Sep-26	$   5,493.40
	Oct-26	$   5,493.40
	Nov-26	$   5,493.40
	Dec-26	$   5,493.40
	Jan-27	$   5,493.40
	Feb-27	$   5,597.77
	Mar-27	$   5,597.77
	Apr-27	$   5,597.77
	May-27	$   5,597.77
	Jun-27	$   5,597.77
	Jul-27	$   5,597.77
	Aug-27	$   5,597.77
	Sep-27	$   5,597.77
	Oct-27	$   5,597.77
	Nov-27	$   5,597.77
	Dec-27	$   5,597.77
	Jan-28	$   5,597.77
	Feb-28	$   5,704.13
	Mar-28	$   5,704.13
	Apr-28	$   5,704.13
	May-28	$   5,704.13
	Jun-28	$   5,704.13
	ii. Annual tax amount
	Year	Annual Salary	Tax Amount
	2020-2021	$   60,475.00	$   11,201.38
	2021-2022	$   61,624.02	$   11,574.81
	2022-2023	$   62,794.88	$   11,955.34
	2023-2024	$   63,987.98	$   12,343.09
	2024-2025	$   65,203.76	$   12,738.22
	2025-2026	$   66,442.63	$   13,140.85
	2026-2027	$   67,705.04	$   13,551.14
	iii. After-tax income per month
	Month	Salary (Before tax)	Salary (after tax)
	Jul-21	$   5,000.00	$   4,066.55
	Aug-21	$   5,000.00	$   4,066.55
	Sep-21	$   5,000.00	$   4,066.55
	Oct-21	$   5,000.00	$   4,066.55
	Nov-21	$   5,000.00	$   4,066.55
	Dec-21	$   5,000.00	$   4,066.55
	Jan-22	$   5,000.00	$   4,066.55
	Feb-22	$   5,095.00	$   4,161.55
	Mar-22	$   5,095.00	$   4,161.55
	Apr-22	$   5,095.00	$   4,161.55
	May-22	$   5,095.00	$   4,161.55
	Jun-22	$   5,095.00	$   4,161.55
	Jul-22	$   5,095.00	$   4,130.43
	Aug-22	$   5,095.00	$   4,130.43
	Sep-22	$   5,095.00	$   4,130.43
	Oct-22	$   5,095.00	$   4,130.43
	Nov-22	$   5,095.00	$   4,130.43
	Dec-22	$   5,095.00	$   4,130.43
	Jan-23	$   5,095.00	$   4,130.43
	Feb-23	$   5,191.80	$   4,227.24
	Mar-23	$   5,191.80	$   4,227.24
	Apr-23	$   5,191.80	$   4,227.24
	May-23	$   5,191.80	$   4,227.24
	Jun-23	$   5,191.80	$   4,227.24
	Jul-23	$   5,191.80	$   4,195.53
	Aug-23	$   5,191.80	$   4,195.53
	Sep-23	$   5,191.80	$   4,195.53
	Oct-23	$   5,191.80	$   4,195.

c. [12 marks]i. Calculate Judy’s monthly loan repayment amount. Assume that the re-payment is paid at the beginning of each month.ii. Calculate the accumulated deposit value, ifx= 35%.iii. Calculate...

Solution

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment