Viraj Shah

3462191
ACTL 2111
Summary Report
For this assessment task, we were assigned a loan valuation question that corresponded to
a similar looking real life case scenario. The context of the assessment task emphasized on a
just turned 50 year old man that took the option of a reverse mortgage loan. The task was
distributed among seven questions with each question depending on the previous one.
The first question was asked to calculate the accumulation value of Marweyis savings
account after two months which had an initial amount of $30,500. The interest was paid at
3% p.a. (convertible quarterly, credited monthly). In my opinion this was a basic question
aimed at us getting started smoothly so that the later parts can become less difficult. This
question was done in the 1
st
worksheet of the excel file. To account for quarterly frequency,
the annual rate was converted to effective rate in order to find the actual interest payments
that were credited monthly. After setting up initial parameters, various basic arithmetic
excel functions were used. The results of question 1 can be seen in the diagram:

Furthermore, it was also asked to provide a schedule on how Marweyi's salary will increase
through time until he goes into retirement. The last income value before retirement is
depicted below:-


The 3% depicts the annual rise from the previous years salary due to inflation.

Question 2 asked us to simulate the nominal fortnightly mortgage rates corresponding to
the repayment period until retirement. I was a bit clueless in the beginning since I hadnt
had practice in modelling and generating random numbers that followed a normal
distribution as this was important to calculate the Wiener increment that played a
significant role in calculating the corresponding interest rates. Luckily, the formula was
already given in the question and it only required to copy that onto the excel spreadsheet.
The formula of datedif() was used to calculate the number of fortnights between Marweyis
50
th
birthday and 65
th
birthday (after which he goes into retirement). Furthermore, it was
also necessary that the random generated rate stayed between 5% and 9% in order to
follow the mortgage interest rate collar as described in the question. The if formula was
used and nested twice in order to restrict the rate between 5% and 9%. The corresponding
effective fortnightly rate was found just by basically dividing the annual rate by 26. Since the
rates were randomly generated, pressing the F9 key automatically refreshed and randomly
generated new rates. Some of the output is depicted below:-

Date Opening Balance Interest Credited Closing Balance
1/04/2014 $30,500.00 $0.00 $30,500.00
1/05/2014 $30,500.00 $76.25 $30,576.25
1/06/2014 $30,576.25 $76.25 $30,652.50
1/1/2029-31/03/2029 $3,894.92 3%
Fortnight # Wiener Increment Interest rate (p.a.) Effective fortnightly rate
1 - 5.60% 0.215%
2 -0.018451 5.57% 0.214%
3 -0.003979 5.56% 0.214%
4 0.052548 5.66% 0.218%
5 0.046686 5.75% 0.221%
6 -0.053807 5.65% 0.217%
7 0.006749 5.66% 0.218%
Viraj Shah
3462191
ACTL 2111
Question 3 and question 4 were, in my opinion, the most difficult sets of questions in the
task. These two questions required advanced MS Excel skills in order for them to function
perfectly. Question 3 was about developing a mortgage calculator on a spreadsheet showing
the fortnightly repayments from inception of the loan. It should also include all components
of a loan schedule such as the simulated mortgage rates together with the corresponding
interest payments, etc. Question 4 was a more generalised version of question 3 in that the
calculator developed should be compatible with various payment frequencies such as daily,
weekly, monthly, etc. This was a pretty difficult thing to implement since every frequency
had its own constant parameters e.g. speed of mean reversion of interest rate process, long-
term mean rate, volatility etc. To account for all seven scenarios to give an exact output
within a cell was difficult. A series of if formulas were used and they were also nested
multiple times in order to incorporate all the frequencies. The constant parameters table
was copied straight from the assessment task which came in handy. To calculate payment,
pmt formula was used with a variable rate that was calculated in the accompanying cell.
This question would have been much easier to do with the help of VBA instead of using
excel functions. Some of the snapshots of these questions worksheet are portrayed below:-




Question 5 and question 6 were relatively easy compared to the previous questions.
Question 5 asked to basically calculate the outstanding balance at Marweyis death
assuming he dies at age 85 and stops payments after retiring. This was basically
compounding the market value of the house at age 65 by the specified interest rate i.e. 2%
for 20 more years. However, it was important to set the frequency of the payment to 26 per
year since this was the original tenure and also the outstanding balance in this worksheet
corresponded to the cell reference in the previous sheet. The result summary is shown
below:-

Pressing the F9 key refreshed the value of the outstanding balance at death although it
mostly stayed between $430,000 and $450,000.
Question 6 was the one that asked to account for the reverse mortgage aspect of the
assessment task. It incorporated the appreciation of the house with time. It asked to
Market Value of House $450,000.00
Initial Deposit $30,652.50
Loan Principle $419,347.50
Loan Term (years) 30
Repayment Frequency (in a year) (dropdown) 26
Number of repayments 780
Interest rate(p.a.) Restricted rate (p.a.) Effective Interest Rate Payment Interest Principal Paid Outstanding Balancing
$419,347.50
0.056 5.600% 0.21538% $1,110.56 $903.21 $207.35 $419,140.15
0.055894935 5.589% 0.21498% $1,109.28 $901.07 $208.21 $418,931.93
0.055008731 5.501% 0.21157% $1,098.52 $886.34 $212.18 $418,719.75
0.054211735 5.421% 0.20851% $1,088.89 $873.06 $215.83 $418,503.92
0.053638786 5.364% 0.20630% $1,082.00 $863.39 $218.61 $418,285.31
Question 5
Outstanding Balance at retirement
(note- the frequency in prev. sheet should be set at
26) $294,856.02
Interest Rate (p.a., effective) 2%
expected lifetime after retirement (years) 20
Outstanding balance at death 438140.5304
Viraj Shah
3462191
ACTL 2111
calculate the expected home value at the end of year of Marweyi's death, the amount of
money that will be paid to the bank and also the excess (if remaining) that would be paid to
Marweyis beneficiaries. The annual effective appreciation rate was converted to fortnightly
effective rate. Furthermore, it was assumed that the appreciation rate was applied to
market value of the house after it got deducted by Marweyis withdrawals. The results are
summarised below:-


Overall, in my opinion, this assessment task was very well designed as it relied on the
technical skills required for various calculations and also how the various data are
represented and interpreted. This could give insight to what various financial careers
actually involve.
Initial Withdrawal $250
Annual Increase $10
Appreciation rate (p.a., effective) 1.50%
Effective Fortnightly rate 0.057%
Total fortnights between 1/06/2014
and 31/03/2029 387
Market Value of house at retirement $561,615.78
Market Value at death $604,789.72
Outstanding balance at death $435,075.84
Benefit paid to beneficiaries $169,713.88