smc-6926-25-Spreadsheets

Financial Maths, STD2 EQ-Bank 29

Shown below is part of the output from a spreadsheet used to model a 25-year reducing balance loan with equal monthly repayments and a constant interest rate, $r$, expressed as a decimal.

Note that many rows and values have been removed from the output shown.

Show that the amount owing at the end of month 120 is $407 860.99. (4 marks)

--- 7 WORK AREA LINES (style=lined) ---

Show Answers Only

$\text{See worked solution}$

Show Worked Solution

$\text{Find } r:$

$\text{Interest}$	$= r \times \text{amount owing}$
$3500$	$= r \times 500\,000$
$r$	$= 0.007$

$\text{Find monthly repayment using PVIF table:}$

$\text{At } N = 300\ \text{ and }\ r = 0.007,\ \text{ PVIF} = 125.23492$

$\text{Monthly repayment}= \dfrac{500\,000}{125.23492}= \$3992.50$

$\text{Amount owing at start of month 120:}$

$\text{Amount owing at end of month 119} = \$408\,990.56$

$\therefore\ \text{Interest charged in month 120}$	$= 0.007 \times \$408\,990.56$
	$= \$2862.93$

$\text{Amount owing at end of month 120:}$

$= \$408\,990.56+\$2862.93-\$3992.50$

$= \$407\,860.99 \quad \checkmark$

Financial Maths, STD2 F4 2024 HSC 21

William has a reducing balance loan on which he owes $5590. He makes monthly repayments of $110.

The loan company charges interest at 24% per annum, compounded monthly.

The spreadsheet shows some of the information for the next two months of the loan.

Complete the entries in the spreadsheet to show the balance owing on the loan at the end of two months. (2 marks)

--- 4 WORK AREA LINES (style=lined) ---

Explain why the loan will never be repaid if William continues to make repayments of $110 per month. (1 mark)

--- 3 WORK AREA LINES (style=lined) ---

Show Answers Only

b. $\text{The interest charged exceeds the amount that is repaid.}$

$\text{Interest charges will gradually increase with the monthly}$

$\text{repayment staying the same \(\Rightarrow$ loan will never be repaid.}\)

Show Worked Solution

$\text{Calculations:}$

$\text {Cell E2 }= 5590+111.80-110=\$ 5591.80$

$\text{Cell B3 }=\text{ Cell E2}$

$\text{Cell C3}=5591.80 \times 0.02 = \$111.84 \ \text{(monthly int. rate = 2%)}$

$\text{Cell E3}=5591.80+111.84-110 = \$5593.64$

b. $\text{The interest charged exceeds the amount that is repaid.}$

$\text{Interest charges will gradually increase with the monthly}$

$\text{repayment staying the same \(\Rightarrow$ loan will never be repaid.}\)

Financial Maths, STD2 F4 2010 HSC 25b

William wants to buy a car. He takes out a loan for $28 000 at 7% per annum interest for four years.

Monthly repayments for loans at different interest rates are shown in the spreadsheet.

How much interest does William pay over the term of this loan? (2 marks)

--- 5 WORK AREA LINES (style=lined) ---

Show Answers Only

`$4183.52`

Show Worked Solution

♦ Mean mark 42%
MARKER’S COMMENT: An incorrect table value used correctly in the following calculations received half-marks here. Show your working!

`text(Loan) = $28\ 000,\ \ \ \ r =\ text(7% p.a.)`

`text(Monthly repayment = $670.49`

`text(# Repayments) = 4 xx 12 = 48`

`text(Total repaid)= 48 xx 670.49= $32\ 183.52`

`:.\ text(Interest paid)`	`=32\ 183.52-28\ 000`
	`=$4183.52`

Financial Maths, STD2 F4 2010 HSC 28a

The table shows monthly home loan repayments with interest rate changes from February to October 2009.

What is the change in monthly repayments on a $250 000 loan from February 2009 to April 2009? (1 mark)

--- 3 WORK AREA LINES (style=lined) ---
Xiang wants to borrow $307 000 to buy a house.

Xiang’s bank approves loans for customers if their loan repayments are no more than 30% of their monthly gross salary.

Xiang’s monthly gross salary is $6500.

If she had applied for the loan in October 2009, would her bank have approved her loan?

Justify your answer with suitable calculations. (3 marks)

--- 6 WORK AREA LINES (style=lined) ---
Jack took out a loan at the same time and for the same amount as Xiang.

Graphs of their loan balances are shown.

Identify TWO differences between the graphs and provide a possible explanation for each difference, making reference to interest rates and/or loan repayments. (2 marks)

--- 5 WORK AREA LINES (style=lined) ---

Show Answers Only

a. `text(Monthly repayments decrease by $15)`

b. `text(S)text(ince repayments of 1987.29 > 1950, the loan)`

`text(would not have been approved.)`

c. `text(Differences)`

`text(1. Jack’s loan balance falls more sharply for first 12 years)`

`text(2. Jack’s loan balance falls less sharply between years 12-30.)`

`text(Explanation)`

`text(1. Jack made larger repayments for first 12 years, OR)`

`text(Jack made the same repayments but had a lower interest rate)`

`text(for the first 12 years.)`

`text(2. Jack made smaller repayments in years 12 – 30.)`

Show Worked Solution

a.	`text(Repayment)\ text{(Feb 09)}`	`= 1588`
	`text(Repayment)\ text{(Apr 09)}`	`= 1573`

`text(Difference) = 1588-1573 = 15`

`:.\ text(Monthly repayments decrease by $15)`

♦ Mean mark 39%
MARKER’S COMMENT: Borrowing $307,000 can be achieved by borrowing $300,000, and then `7` times the table repayment value for borrowing $1000.

b. `text(Loan) = $307\ 000`

`text{Repayments (Oct 09)}`	`= 1942 + (7 xx 6.47)= 1942 + 45.29`
	`= $1987.29\ text(per month)`

`text(30% Gross salary)= 6500 xx\ text(30%)= $1950\ text(per month)`

`:.\ text(S)text(ince repayments of $1987.29 > $1950, the loan)`

`text(would not have been approved.)`

c. `text(Differences)`

`text(1. Jack’s loan balance falls more sharply for first 12 years.)`

`text(2. Jack’s loan balance falls less sharply between years 12-30.)`

`text{Explanation(s)}`

♦ Mean mark 36%
MARKER’S COMMENT: Explanations were generally poor and many failed to refer directly to the graphs shown, or reference Xiang or Jack directly.

`text(1. Jack made larger repayments for 1st 12 years, OR)`

`text(Jack made the same repayments but had a)`

`text(lower interest rate for the first 12 years.)`

`text(2. Jack made smaller repayments in years 12-30.)`

Financial Maths, STD2 F4 2011 HSC 22 MC

Ying borrowed $250 000 to buy a house. The interest rate and monthly repayment for her loan are shown in the spreadsheet.

What is the total interest charged for the first four months of this loan?

$6364.32
$6366.11
$6369.67
$6376.25

Show Answers Only

`A`

Show Worked Solution

`text(Month 3)`

`P+I-R=251\ 032.04-1871.94=$249\ 160.10`

`text(Month 4)`

`P=$249\ 160.10`

`:.I=249\ 160.10xx0.0765/12=$1588.40`

`:.\ text(Total interest)=1593.75+1591.98+1590.19+1588.40=$6364.32`

`=> A`

\(\text{Interest}\)	\(= r \times \text{amount owing}\)
\(3500\)	\(= r \times 500\,000\)
\(r\)	\(= 0.007\)