Reverse Engineer Loan Payment for amortization

Question

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

related to (Amortization Payments with a delay in initial payments)

Answer 1

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...

Answer 2

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.
3. Adjust up or down;
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.