Thread: Mortgage.

I have absolutely no idea how to attempt this question at all ):

A bank lends money to its customers on mortgage repayable by equal fortnightly instalments using a quoted interest rate of 5.5% per annum. The outstanding balance is initially the amount of the loan and the balance is reduced by any instalments received. At the end of each month simple interest is charged based on the average daily balance for the month and the number of days in the month. The bank rounds all interest charges up to the next fifty cents.

Estimate the fortnightly instalment required to extinguish this loan on the 24th of June 2024 making adjustments to allow for months of different length, the fifty cent interest rounding adjustment, and leap years; explain and justify the adjustments you make.

Thank you for your time and help!

1) You cannot have "NO IDEA". If this is so, you simply have not been paying attention at all. Please abandon this idea of "no idea".

2) What's fortnight?

After that, break it down.

1) Pick a loan size. It will be easier with actual numbers.
2) Payments are made every 14 days. 365/14 = 26.071 periods/year unless 366/14 = 26.143 periods/year.
3) Interest is charged every month.
4) Quoted interest as 5.5% per annum gives 0.055*31/365 = 0.00467123 except when 0.055*30/365 = 0.00452055 or when 0.055*29/366 = 0.004357923 or when...well, it is hoped that you get the picture.

This is a wonderful (albeit relatively complicated) spreadsheet problem. I think you will have to count days, rather than weeks or months or anything else. Unfortunately, if you knock it out with an excellent spreadsheet, it will be harder to justtify adjustments, since you won't be making any.

I apologize for the figure of speech.

It is a question with respect to a loan of $100,000 advanced on 15 June 2009.

Awesome. What is your plan for this $100,000 loan advanced on 15 June 2009?

15 June 2009: Balance = $100,000.00
29 June 2009: Balance = $100,000 - Payment
30 June 2009: Interest is Due

[15*100000 + 1*(100000-Payment)]/16 = Average Daily Balance = ADB

Interest = P*r*t = ADV*0.055*(16/365)
30 June 2009: Balance = $100,000 - Payment + Interest

13 July 2009: Payment
27 July 2009: Payment
31 July 2009: Interest is Due

Continue.

But there was a example provided in the question that says,

For example, if the balance at the beginning of March is $100,000 and an instalment of $1,200 is received on March 14th, then simple interest (Assuming 365 days a year) will be charged for 14 days on $100,000 and for 17 days on $(100,000 - 1,200) and then rounded to the next 50 cents.

Hm, I think I'll have to do this in a spreadsheet but I don't really know where to start.

Why the "but". This is exactly what I have suggested. My example uses a partial month with only 16 days.

As far as the spreadsheet goes, I wouls start with four columns.

1) Date - You may need daily detail. Monthly or weekly are likely to be inadequate. 30*365.25 = about 11,000 but that should be no trouble. Get used to scrolling and framing column headers.

2) Balance - Once you get the other columns, this should be trivial.

3) Payment - This one is easy. After the first two weeks, just have it copy whatever happens 14 days earlier.

4) Interest - This one is a little tricky for two reasons.

4a) Months are different sizes. You may need a fifth column to accumulate interest accruals and use this column just to capitalize them. I'm thinking of a nice usage of the "=sumif()" function, looking back 31 days and picking only the days from the month just ending. Maybe it's easier than that. I haven't actually sat down to code it.

4b) Years change sizes, too.

Good luck.

P.S. Don't use any figure of speech that is discouraging and depressing.