# Reverse Engineer Loan Payment for amortization

I'm starting to question the Payment amount generated by the software my lender is using, but it could be my calculations that are wrong. I would like confirmation before I proceed. Is my approach (math below) correct, or is the software (linked images) correct?

(Updated Images) http://snag.gy/3w54B.jpg http://snag.gy/ncAki.jpg

The Periodic Payment Amount the software calculates is $1,412.4. Parameter Values: Principal:$100,000
Interest Rate: 8.5%
Periods: 120
Payment Frequency: Monthly
Disbursal Date: January 1st 2014
Initial Payment Date: January 1st 2015
Compounding Semi-Anually


With the given values I think it's deferred for 12 periods.

My calculations:

Step 1: Calculate Interest Accrued.

Effective Annual Rate = (1 + 8.5/2)^2 = 1.08680625
Rate Per Month        = EAR^(1/12) = 1.0868062^(1/12) = 1.006961062
Rate for 12 months    = 1.006961062^(12) = 1.08680625


Interest Accrued for 12 months = 1.0868062*100000 = $8,680.62 Step 2: Calculate Monthly Payment Monthly Payment = (PxI)/(1-(1+I)^(-N)) N = 120 - 12 = 108 (because 12 periods was deferred) P =$100,000 + 8680.62
I = .006961062


Monthly Payment = $1434.86 • Is the screenshot from you or the lender? – Noah Sep 9 '14 at 20:50 • That's the screenshot from the software(lender) – user3276954 Sep 9 '14 at 20:51 • if you close on the 15th you have to add the interest for the first 45 days until you make your first payment – Mark Monforti Sep 9 '14 at 21:08 • I don't have a huge amount of experience with semi-annually compounding interest, and the closest I can get is ~$1417, but that's using a completely different set of calculations – Noah Sep 9 '14 at 21:14
• Hi Noah, that's still relatively closer, may I ask what were your steps. Yeah semi-annually is a Canadian thing :P – user3276954 Sep 9 '14 at 21:21

You are using the formula for an ordinary annuity ("Ordinary" is a term from math of finance, not just a dismissive adjective!)

This formula assumes that the first payment comes one payment period after the disbursement. In your example, the first payment is made twelve payment periods after the disbursement; you should only increase the principal amount by eleven months of interest.

I don't see why the deferral of some payments should reduce the number of payments. The question should state either the number of payments, or the date of the last payment. Anything else leads to confusion; lenders hate confusion...

EDIT:

Upon further consideration, it would appear that the lender is using a novel method of calculating the amortization of this loan. Note the interest for the months of January, February, and March goes $709.50,$640.62, and then back up to $709.50 The monthly interest on the declining balance cannot go up for a later payment. The only possible explanation is that the inventive lender is charging a daily rate compounded each day and then reducing the balance at the end of the month by the monthly payment. Note that the ratio of the two interest charges, 709.50/640.62 is 1.10752 The ratio of the number of days, 31/28 is 1.10714; the two values are too close to be a coincidence. Given this interest method, the only way to check it is with a brute force 3700 row spreadsheet. Don't forget leap-years... • I had the same thoughts as you, but its maturity date is dependent on disbursal date. It's 120 periods after disbursal date. Not 120 periods after initial payment date. May I ask what do you mean by brute force spreadsheet? – user3276954 Sep 10 '14 at 13:22 • Brute force: take yesterday's balance, compound with one days interest, check if a monthly payment is due and should be subtracted, and then go to the next day. Repeat for as many rows as necessary, at least 3650. The irregular nature of the payments, on a day-to-day basis, makes any formula impossible... – DJohnM Sep 10 '14 at 23:50 • Subtracted by what value though? – user3276954 Sep 11 '14 at 4:25 You will have to generate a 108 or 120 line table. They are deferring the first payment which puts you$8680.62 behind in interest. Once payments start all payments are for interest until you are no longer in behind, keeping your principal at 100,000 for several months. Of course that principal balance is used to calculate additional interest. It doesn't appear that the outstanding interest is used in the monthly interest calculation.

The lender also calculated the interest at a daily rate: The February 1 and April 1 payment have the same interest amount. The March 1 interest is smaller. It is only (28/31) as large.

Of course these daily interest amounts are recalculated every 6 months. I assume the monthly interest amount will get smaller as the principal is reduced, but can't tell because your screen capture doesn't show enough lines.

It is possible that they used an iterative algorithm to calculate a level payment amount to result in a zero balance at the end of 108 or 120 months.

The answers to all these calculations is in the loan paperwork.

After looking at the graphic which showed more lines:

They calculated it iteratively:

1. Estimated a monthly payment;
2. Determined what the balance was 120 months later.
4. Back to step 2.

The complexity in your case is:

• zero payments for 12 months,
• Then applying all payments against new and old interest for 11+ months, using a daily interest rate. (but interest on the back interest is not charged)
• Then traditional payments with a daily interest rate until the loan is paid back

Using their numbers I get 109 payments of 1412.75 almost exactly the calculated payments of 1412.40. During phase 3 of the payment schedule Is where I can't get my calculated interest amount to match the tables interest amount, they differ by about 6 cents per day.

• Hi, I'm trying to figure out the Payments. I understood the interest part. I'm not quite sure if this answers my question, I was looking towards how the payment was calculated. I dont know if you are referring to "their" loan book or what. – user3276954 Sep 10 '14 at 13:17
• I based everything on the graphic attached to your question. Without the entire file there is no way to know what happens in 6 months or when the back interest has been paid off. Total the interest, add the principal, divide by 108 or 120; and that is the monthly payment. – mhoran_psprep Sep 10 '14 at 13:19
• I have more of the photo would you like me to update it? I just thought it wasn't necessary. I've updated it, I can post more of it if needed. – user3276954 Sep 10 '14 at 13:22
• There's 108 payment lines not 120. I posted the first sheet and the last sheet. – user3276954 Sep 10 '14 at 13:24
• "Total the interest, add the principal, divide by 108 or 120" works but in most cases Total interest isn't given, and how can I calculate that before getting payments? – user3276954 Sep 10 '14 at 14:08