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]

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.

Could it be the 26 July payment they assume was paid on 23 July?

I will check the actual bank statement for what happened in July 2017.

How do you share excel files on this site?

 

[joeu2004]

Please edit quoted text to include only the smallest relevant text. It really upsets one moderator (of another similar forum?), who takes out her frunstrations on me, even though I am very judicious about what I quote.

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

Could it be the 26 July payment they assume was paid on 23 July?

My mistake, sort of.

Interest of 1501.50 seems to be based on 26 days with a balance of 373076.40 on Jun 26; that is:

373076.40*26*5.65%/365

But the balance of 371574.90 seems to be 373076.40 - 1501.50. That calculation that does not make sense to me unless they assume a payment of 3003 on Jul 23 (why?) and the balance is also paid on Jul 23 (i.e. no interest for that day).

How do you share excel files on this site?

I explained that in my first response.

 

[swilson2006]

Please edit quoted text to include only the smallest relevant text. It really upsets one moderator (of another similar forum?), who takes out her frunstrations on me, even though I am very judicious about what I quote.




My mistake, sort of.

Interest of 1501.50 seems to be based on 26 days with a balance of 373076.40 on Jun 26; that is:

373076.40*26*5.65%/365

But the balance of 371574.90 seems to be 373076.40 - 1501.50. That calculation that does not make sense to me unless they assume a payment of 3003 on Jul 23 (why?) and the balance is also paid on Jul 23 (i.e. no interest for that day).




I explained that in my first response.

I think those are the figures for early repayment.

So you know algorith they use now?

Would you happen to have an excel file I could use?

Ah yes the file sharing....

 

[joeu2004]

@swilson2006.... FYI, I haven't forgotten about you. Just haven't had time to look at your problem further. I expected to do that this morning, but it didn't happen (sigh). Let me know if it's still worth my time to own up to my promises, or if you got answers another way.

 

[swilson2006]

@swilson2006.... FYI, I haven't forgotten about you. Just haven't had time to look at your problem further. I expected to do that this morning, but it didn't happen (sigh). Let me know if it's still worth my time to own up to my promises, or if you got answers another way.

No my friend, waiting on a working file with solutions from you. I do appreciate your personal time helping me with this. Once I see the formulas in excel I should be able to follow whats going on and apply it to another loan I have to forecast till last payment.

Do you think you can look at it soon?

Thanks again

 

[joeu2004]

@swilson2006.... Again, I'm very sorry for failing to provide a solution. I'm in the middle moving my mother's things out of her apartment; exhausting work. And I'm negotiating the sale of her house. I'm sure my promises seem hollow at this point, but I do still intend to provide a solution (Excel file). Please let me know if you are still waiting for me (patiently), or if you got answers another way.

 

[swilson2006]

@swilson2006.... Again, I'm very sorry for failing to provide a solution. I'm in the middle moving my mother's things out of her apartment; exhausting work. And I'm negotiating the sale of her house. I'm sure my promises seem hollow at this point, but I do still intend to provide a solution (Excel file). Please let me know if you are still waiting for me (patiently), or if you got answers another way.

Thx. Still waiting. No other members who can help?