Excel Variable Loan Amortisation

[swilson2006]

Hi there

I cannot seem to get the mortgage/loan bank statement replicated in excel. Can someone please help? I think it has something to do with when they change rates based on the number of day. I think they use exact days.

You can see at the top of the statement the rates and the date they changed. This should be enough to calculate the repayments but I seem to be getting different repayments than those on the statement. The rate from 16 August 2016 is 5.70% and then 5.65% from May 2017. I want to exrapolate the statement till the end of payment but I cannot get the same payment and interest figures as they do.

I hope some expert on amortisation of loans can help.

Thanks so much. Much appreciated.

S

 

[swilson2006]

Somebody please help or tell me where to post.....

 

[joeu2004]

What's your rush?

Seriously: do you have a need for a resolution soon?

I intend to look at the details a little later.

The images are difficult for my tired eyes to read, even when I view them separately and try to zoom. I think I'm able to muddle through it. But it would be helpful (to me) if you entered the data into a spreadsheet, then uploaded the Excel file to a file-sharing website (e.g. box.net/files) and post the share/public URL in a response here. Test the download URL first, being careful to log out of the file-sharing website. Some participants complain that they cannot or will not download such files; they prefer that you post numbers in a table here. It's up to you.

First impressions.... We can use the numbers for the payments on Sep 26 and Oct 26, 2016 to get a sense for the lender's numbers and algorithms. My prelim results suggest:

1. The simple interest is accumulated daily (as usual), but not compounded (a common misconception re loans).

2. The daily interest rate is the annual rate divided by 365 (not 366 in 2016; and the annual rate is not a compounded rate).

3. The stated interest rate (5.70%) is an approximation. The actual rate is about 5.6985%.

But I'm getting some variability based on those assumption. The root cause might be that the lender rounds some/all numbers on the statement, but maintains numbers to greater precision behind the curtain.

These are the details that I want to explore further, when I have time.

 

[swilson2006]

What's your rush?

Seriously: do you have a need for a resolution soon?

I intend to look at the details a little later.

The images are difficult for my tired eyes to read, even when I view them separately and try to zoom. I think I'm able to muddle through it. But it would be helpful (to me) if you entered the data into a spreadsheet, then uploaded the Excel file to a file-sharing website (e.g. box.net/files) and post the share/public URL in a response here. Test the download URL first, being careful to log out of the file-sharing website. Some participants complain that they cannot or will not download such files; they prefer that you post numbers in a table here. It's up to you.

First impressions.... We can use the numbers for the payments on Sep 26 and Oct 26, 2016 to get a sense for the lender's numbers and algorithms. My prelim results suggest:

1. The simple interest is accumulated daily (as usual), but not compounded (a common misconception re loans).

2. The daily interest rate is the annual rate divided by 365 (not 366 in 2016; and the annual rate is not a compounded rate).

3. The stated interest rate (5.70%) is an approximation. The actual rate is about 5.6985%.

But I'm getting some variability based on those assumption. The root cause might be that the lender rounds some/all numbers on the statement, but maintains numbers to greater precision behind the curtain.

These are the details that I want to explore further, when I have time.

Because I've been sitting on it for days it been bugging my **** on why I can't get back to the same result as the lender.

Do you still need me to type out the images?

I appreciate your help Joe.

 

[joeu2004]

Do you still need me to type out the images?

Yes, that would be greatly appreciated. The image is either too small or too fuzzy when I zoom in. It will be very difficult for me to read more than a few lines.

 

[joeu2004]

Do you still need me to type out the images?

Never mind. I've done it myself already. (sigh)

 

[swilson2006]

Never mind. I've done it myself already. (sigh)

Oh - I just finished it now - sorry

How do I attach the file on here?

Thanks so much for your help with this.

 

[joeu2004]

I need to put together a usable Excel file for you. Mine is fully of experimental calculations.

But if you are a skilled Excel user, perhaps some information now is useful, if not sufficient.

(Honestly, I should not be spending my time on such distractions, as interesting as they might be.)

First, the payments....


1. It appears that there are 194 regular payments remaining after the 5/26/2017. The regular payment starting with 6/26/207 is calculated as follows:

=ROUND(PMT(5.65%/12, 194, -374227.61), 2)

Note that the annual rate is rounded to 2 percentage decimal places (basis points).

(I believe the actual annual rate has more precision and differs from the rounded annual rate. More on that in a separate posting later.)


2. Likewise, the 203 regular payment remaining after 8/26/2016 is calculated as follows:

=ROUND(PMT(5.7%/12, 203, -384614.49), 2)


3. The "transitional" payments, for the month when the annual rate changes, are based on the weighted average annual rate.

a. For the payment period ending 8/26/2016 (with 204 payments remaining):

=ROUND(PMT((20*5.9%+11*5.7%)/31/12,204,-385689.38), 2)

where 20 is DATE(2016,8,14)-DATE(2016,7,26), 11 is DATE(2016,8,26)-DATE(2016,8,15), and 31 is DATE(2016,8,26)-DATE(2016,7,26).

(I derived the rounded annual rate of 5.90% before 8/16/2016 from the data. I'll have to explain that later.)


b. For the payment period ending 5/26/2017 (with 195 payments remaining):

=ROUND(PMT((18*5.7%+12*5.65%)/30/12,195,-375428.19), 2)

where 18 is DATE(2017,5,14)-DATE(2017,4,26), 12 is DATE(2017,5,26)-DATE(2017,5,14), and 30 is DATE(2017,5,26)-DATE(2017,4,26).

-----

That's all for now. Back later, I hope.

 

[swilson2006]

I need to put together a usable Excel file for you. Mine is fully of experimental calculations.

But if you are a skilled Excel user, perhaps some information now is useful, if not sufficient.

(Honestly, I should not be spending my time on such distractions, as interesting as they might be.)

First, the payments....


1. It appears that there are 194 regular payments remaining after the 5/26/2017. The regular payment starting with 6/26/207 is calculated as follows:

=ROUND(PMT(5.65%/12, 194, -374227.61), 2)

Note that the annual rate is rounded to 2 percentage decimal places (basis points).

(I believe the actual annual rate has more precision and differs from the rounded annual rate. More on that in a separate posting later.)


2. Likewise, the 203 regular payment remaining after 8/26/2016 is calculated as follows:

=ROUND(PMT(5.7%/12, 203, -384614.49), 2)


3. The "transitional" payments, for the month when the annual rate changes, are based on the weighted average annual rate.

a. For the payment period ending 8/26/2016 (with 204 payments remaining):

=ROUND(PMT((20*5.9%+11*5.7%)/31/12,204,-385689.38), 2)

where 20 is DATE(2016,8,14)-DATE(2016,7,26), 11 is DATE(2016,8,26)-DATE(2016,8,15), and 31 is DATE(2016,8,26)-DATE(2016,7,26).

(I derived the rounded annual rate of 5.90% before 8/16/2016 from the data. I'll have to explain that later.)


b. For the payment period ending 5/26/2017 (with 195 payments remaining):

=ROUND(PMT((18*5.7%+12*5.65%)/30/12,195,-375428.19), 2)

where 18 is DATE(2017,5,14)-DATE(2017,4,26), 12 is DATE(2017,5,26)-DATE(2017,5,14), and 30 is DATE(2017,5,26)-DATE(2017,4,26).

-----

That's all for now. Back later, I hope.

I am definetely not as advanced as you. I would very much appreciate the amortization table.

They said there was 1501.50 interest in July 2017. With only 192 periods left. So you agree with that?

A working excel file will be greatly appreciated.

 

[joeu2004]

They said there was 1501.50 interest in July 2017. With only 192 periods left. So you agree with that?

And I believe that was as of Jul 23, not Jul 26 when interest would be charged, normally.

Even so, 1501.50 seems low for those 27 days, based on the ending balance on Jun 26 of 373076.40.

Moreover, normally there would be 193 payments remaining after the payment on Jun 26, based on my assumption of 194 payments remaining after the May 26 payment, which is demonstrated by the PMT calculation.

So I presume that there was one or more early payments or some other reduction of principal on or before Jul 23, close enough to Jun 26 to make a significant difference.

But the average daily balance would be between 359256.42 and 359258.82, based on interest between 1501.495 and 1501.50499999999, which rounds to 1501.50. For example: 1501.50*365/27/5.65% = 359257.62 .

So I presume that some or all of those reductions were reversed on or before Jul 23 (with a charge of 20.00 or more?), like what happened in Feb 2017.

-----

PS.... Previously, I asserted that the stated annual rates of 5.70% and 5.65% were rounded from actual annual rates that have more precision. I see now that I was wrong. But we need to be careful about the timing of the payment with respect to when interest is charged and added to the principal.