FUNDS / INVESTMENT IN ACCOUNTS

QUESTION

Financial Mathematics ACTL2001/ACTL5102 Assignment

Session 1 2012

Rudo did Financial Mathematics course as part of her major in Actuarial Studies in 2008. In
this course she covered material on loan amortisation and techniques on developing sinking
fund schedules. After finishing her Bachelor of Commerce degree at the end of 2009, she
was lucky to secure an actuarial analyst role with one of the major insurance companies in
the downtown of Sydney. Rudo started her new role on the 1
st
of February 2010 where her
salary has been paid on fortnightly basis in arrears. From the very fortnight of receiving her
first pay until the 31
st
of May 2012, she has been depositing $1,000 into an investment
account. From February 2010 through to the 31
st
of December 2010, the daily returns of the
investment account were linked to the 91 day (or 3 month) Bank Bill Swap (BBSW)
reference rate such that the return on any given day is equal to BBSW + 100 basis points (I
have attached an excel spreadsheet with the BBSW rates (Note that I have provided the
BBSW rate up to the 31
st
of December 2010). From the 1
st
of January 2011 through to the
31
st
of May 2012, the monthly returns of Rudo’s investment account are lognormally
distributed with parameters given in Equation (1) below.

In December 2011 during a discussion with one of her workmates (Musiwarwo), Rudo
initiated a discussion on which investment options can better suit this post GFC period.
Musiwarwo briefed Rudo on how the housing market has been performing and gave her a
snapshot of the average yields of suburbs around Sydney. From this discussion, Rudo noted
that the Sydney North Shore has been performing pretty well and the outlook sounded very
bright in the short to medium term.

She then thought of buying an investment property in this suburban area. Rudo wishes to
enter the market on the 1
st
of June 2012 where she is expecting to use the accumulated
funds as initial deposit. The property which Rudo wants to buy is currently pegged at $1.5
million and we assume that no one will be interested in buying it before the 1
of June 2012
and this will still be the price of the property. She is thinking of signing up for a variable
interest rate, where the mortgage lender has set the initial rate for June 2012 at 7.5% per
annum convertible monthly. The mortgage rates will be reset on monthly basis such that:
r
1
),05.0,0(log
normal
r
i
i


2
(1)
st
where
r
is the interest rate per annum convertible monthly in the
i
th
i
month. Mortgage
repayments will be made over a 15 year period on fortnightly basis. There is a grace period
of two months before Rudo can start repaying the loan (note that interest will still be charged
on the principal borrowed over the grace period). During this period, she is deciding to
deposit the equivalent fortnightly interest payments in a “sinking fund” the face value of
which will be paid soon after the grace period. Mortgage repayments will kick off in after the
grace period has ended.
Given the above information:

1. Determine how the funds in the investment account will accumulate from the 1
of
February 2010 to the 31
st
of May 2012.
2. Simulate the monthly interest rates over the 15 year period starting from the 1
of
June 2012. You must also show how to obtain the associated effective fortnightly
rates.
3. Determine the accumulated value of the sinking fund contributions over the grace
period.
4. Develop a mortgage calculator in a spreadsheet determining the fortnightly payments
from inception of the loan. The spreadsheet should have a breakdown of interest
payments and the corresponding principal component for each forthnight payment.
Your calculator should automatically update all spreadsheet cells if there is any
change in input parameters such as interest rates, tenure of the loan, frequency of
repayments and initial loan amount, among others.
5. Now suppose that in June 2012 there is another alternative for a fixed interest
mortgage loan set at 8.2% per annum. Develop another loan amortisation schedule
with a breakdown of principal payments and interest payments per fortnight. The two
months grace period still applies for this fixed rate loan.
6. Based on your findings in Part 4 and 5, explain which is the best investment option
between a variable mortgage loan and a fixed interest rate mortgage loan.
7. Write a report summarising the evolution of Rudo’s investments based on your
simulated results from the 1
st
of February 2010 up until the end of the 15 year
investment horizon. Your report should consist of a short summary of your findings in
4 & 5 above. You must also interpret the effects of changing any of the input
variables on your calculator. (Your report may include graphs/ tables where
necessary and should have a maximum of 1500 words.)
st
st
The Due date of this assignment is the 18
th
of May 2012 at 5pm sharp. All
submissions should be made via Blackboard. I will create a submission link in
early May. You must submit both the excel spreadsheet and
the
executive/short summary as either a pdf file or a word document

SOLUTION

1. Determine how the funds in the investment account will accumulate from the 1st of

February 2010 to the 31 st of May 2012

Ans .

Given r_i/r_i-1=lognormal(0.05²), where r_i is the interest rate in the i^th month. Hence if we multiply the interest rate of previous month with mean of lognormal(0,.05²) then we can get the interest rate of the current month.

Given BBSW rates till 31/12/2010,hence if we take out the monthly interest rate of December 2010,by taking mean of the  annual rates given for each day of December and then divide by 12, then we can get the monthly interest rate for Dec.2010, and by above lognormal relation we can estimate interest rates for successive months.

The lognormal value of mean =1.0012507 .

See excel sheet 7 on how av annual interst rates have been calculated.

See excel sheet 9 and 8 on how these interst rates have been applied to get end of period amounts.

Amount for a particular period=Amount for end of previous period +$1000

Thus by end of May 2012 the accumulated amount is=$58211 (excel sheet 8)

2. Simulate the monthly interest rates over the 15 year period starting from the 1st of

June 2012. You must also show how to obtain the associated effective fortnightly

rates.

Ans. Given interest p.a for June 2012 is 7.5% convertible monthly

Interst rate for month of June 2012=7.5%/12=0.625%

From the lognormal relation interst rates (convertible monthly) for successive  months and years can be calculated as given in excel sheet 3

3. Determine the accumulated value of the sinking fund contributions over the grace

Period

Ans.From the question it can be supposed that Rudo starts the sinking fund from June 2012 till Aug 2012(two months).Principal Amount of Loan=Cost of Property-Accumulated funds(see Ans 1)=$1500000-$58211=$1441789

Interest will be charged on $1441789 at the fortnightly interest rates calculated in Ans 2.

Calculations are done in sheet 4.

4. Develop a mortgage calculator in a spreadsheet determining the fortnightly payments

from inception of the loan. The spreadsheet should have a breakdown of interest

payments and the corresponding principal component for each forthnight payment.

Your calculator should automatically update all spreadsheet cells if there is any

change in input parameters such as interest rates, tenure of the loan, frequency of

repayments and initial loan amount, among others.

Ans.Mortgage payments will be made by paying amounts on fortnightly basis so as to include the interest payment and principal payment, in such a way so that the entire loan is paid in 15 years.

The principal amount payable each month can be calculated by dividing the loan amount by the number of fortnights in 15 years= (15*365)/14=391 fortnights,therefore in each fortnight principle amount payable=Loan Amount/391=$1441789/391=$3687.44.With each loan amt repayment ,loan amt will decrease.

In work sheet 5 the loan gets repaid in the yr 2028-29 in the 1st fortnight of November.

In the work sheet each yr starts from Aug and ends in July,i.e here in a year 24 fortnights have been considered.Simulation has been done for 16*24+17=384+7=391fortnights which comes equal to the above no. of fortnights calculated.

5. Now suppose that in June 2012 there is another alternative for a fixed interest

mortgage loan set at 8.2% per annum. Develop another loan amortisation schedule

with a breakdown of principal payments and interest payments per fortnight. The two

months grace period still applies for this fixed rate loan.

Ans . excel sheet 6.The fortnightly interest rate will uniform at (8.2/365)*14%=.3145198%

6. Based on your findings in Part 4 and 5, explain which is the best investment option

between a variable mortgage loan and a fixed interest rate mortgage loan.

Ans. This can be found by totaling the interest paid.The plan with the lesser amount of interest is the better plan

With variable rate of interest the total interest payable for the entire loan period=$886202.144

With fixed rate of interest the total interest payable for the entire loan period=$825781.199

 Hence in this case the fixed plan is better.

7. Write a report summarising the evolution of Rudo’s investments based on your

simulated results from the 1st of February 2010 up until the end of the 15 year

investment horizon. Your report should consist of a short summary of your findings in

4 & 5 above. You must also interpret the effects of changing any of the input

variables on your calculator. (Your report may include graphs/ tables where

necessary and should have a maximum of 1500 words.)

Rudo accumulates $58211 as job savings, from Feb 2010 to May 2012 and she uses this amount to make initial deposit for property loan of $1.5 m. Hence she takes a loan of$ 1441789 ($1.5m-$58211).

Since a part of the principal amount gets repaid every fortnight, hence with increasing time period the total amount given for loan servicing decreases as the interest amount decreases due to decrease in principle amount.

Refering to sheet 6,constructing table on year wise totalamount paid

See excelsheet 10 for graph.

KH80

Please  Click on the  below links to Chat Now  or fill the Order Form !