Amortize a loan that is interest only for 2 years and has a balloon payment

[raycal84]

Hi, I am trying to amortize a loan correctly with the following details:

Loan Amount : 10,800,000
Rate: 3.78%
Monthly period
13 year loan
Start Date 9/1/14
Maturity date 8/1/2027
Balloon Payment 5933802.43

Thanks in advance

[curiouscat408]

@raycal84.... A "loan" that starts on 9/1/2014 with a final payment(?) on 8/1/2027 is only 155 months, not 156 months (13 years), if payments are "in arrears" (end of the period), which is common.

Are the terms of the "loan" (lease?) actually payments "in advance" (beginning of the period)?

Based on that assumption, and assuming a monthly interest rate of 3.78%/12 (*), the interest-only payment for the first 24 months is 10800000*3.78%/12 = 34020 (**).

The balance of the loan is still 10,800,000 after the first 24 months, with 11 years (132 months) remaining. So the approximate monthly payment is:

=PMT(3.78%/12, 132, -10800000, 5933802.43, 1)

The result is 63607.3225053747 . If you choose to round (each lendor / lessor has their own rules for rounding), the last payment will be different.


-----
(*) The monthly rate is not always derived from the annual rate by dividing by 12. It depends on regional regulations and sometimes type of loan.

(**) Some lenders calculate interest on a daily basis. Usually, that is simple interest, not compounded daily. In either case, the monthly interest-only payment might vary.

[raycal84]

Thank you curiouscat408. I am now closer to the number. Not sure why there is a discrepancy between the model and what is attached i.e. the payment amount. Any ideas?

[curiouscat408]

[.... withdrawn .... replaced by subsequent posting ....]

[curiouscat408]

@raycal84.... On second thought, I cannot explain why the lender chose 64,200.68 instead of the result from PMT.

Please do the following for me.


1. The image that you provided was helpful. But for confirmation, please provide the following images:

a. The amortization schedule that includes the first "several" payments after the interest-only subterm. That is, starting with the payment due on 9/1/2016 at least through 12/1/2016, but ideally including some or all payments in 2017.

b. The amortization schedule that includes the last "several" payments. Let's say: all of the payments in 2027.


2. Tell me whether this is a bona fide loan of 10,800,000 in currency; or if this is a lease of property (e.g. equipment, building, etc) that has an initial value of 10,800,000 and a residual value of 5,933,802.43.


3. Tell me whether you believe that payments are at the end or at the beginning of periods.


That information might give me some insight. And it will help me simplify any future explanations and clarifications.

-----

Refer to the attached image and Excel file.

The image that you provided confirms that monthly interest is 3.78%/12.

It also confirms that interest is calculated monthly, not daily.

For a "normally amortized" loan, the regular payment after the interest-only subterm is calculated as follows:

a. For payments at the end of periods (typical loan):

=PMT(3.78%/12, 132, -10800000, 5933802.43), which is 63,807.69 (rounded)

b. For payments at the beginning of periods (typical lease):

=PMT(3.78%/12, 132, -10800000, 5933802.43, 1), which is 63,607.32 (rounded)

However, lenders are free to ask for larger payments. Assuming the same disclosed interest rate, the effect is to pay off the principal faster. That results in a smaller last payment and/or paying off the loan sooner.

Typically, lenders round payments up to the "dollar" or less precision in order to make the payment easier. Who can remember 63,807.69? (wink)

But in your case, the lender has "arbitrarily" chosen the regular payment of 64,200.68 after the interest-only subterm. (*)

The tables in columns M:Q and S:W demonstrate the amortization of the loan with the larger payment for "end payments" and "beginning payments", respectively.

Study the formulas in M6:Q6 and S6:W6.

Note that because of the significantly larger regular payment, the last "end payment" is actually due on 7/1/2027, not 8/1/2027. And that payment is 45,567.93, instead of 64,200.68.

Ostensibly, the final balance ("balloon payment") is also due on 7/1/2027. Whether the lender permits you to delay that payment until the disclosed "maturity" date of 8/1/2027, and whether the lender would charge interest during the delay, is up to the lender to decide -- and to disclose.

Also note that the last "beginning payment" is actually due on 6/1/2027 (!), not 8/1/2027.


-----
(*) On "third thought", I wonder if the regular payment of 64,200.68 is composed of 63,807.69 for the loan plus additional fees.

For example, it is not unusual to add "payment insurance" fees (e.g. PMI for mortgages).

Again, if you provide more of the amortization schedule, I might be able to suss this out.


-----

[raycal84]

So here is the amortization from the bank. It took them a couple of day to send to me.

[raycal84]

Here is the workbook I have been trying to match. I have a refinance table in here as well in the event the loan is refinanced or has been refinanced. Any help would be appreciated as I feel I am in a rabbit hole right now.

[curiouscat408]

So here is the amortization from the bank.

Thanks. That clears things up.

First, if you compare the bank's amortization schedule to mine in columns M:Q, you will see that the balances for the period 25 to 154 agree "pretty closely".

My balance after 154 payments is 5,960,594.49. The bank's balance is 5,960,594.46.

The difference can be explained by the bank's rounding policy.

Ostensibly, the bank seems to round interest to 2 decimal places.

(I will have to do a more thorough check to be sure that is sufficient.)

-----

The more significant difference is how the term "balloon payment" is defined.

ERRATA.... See posting #10.

[curiouscat408]

Here is the workbook I have been trying to match.

Your Excel file has a lot more going on. It also has some errors(?), such as circular references. And frankly, I'm not sure I will know what you are trying to do without any explanation(!).

It will take me some time to study what you are doing.

I do not know if I will have time to do that. If I do, I will comment on it in a few days.

-----

In the meantime, I hope you can incorporate the conclusions from my previous response. ERRATA.... See my later response in posting #10.

And if that is sufficient for you to make your Excel file, please let us know so that I can avoid looking at the Excel file further.

[curiouscat408]

ERRATA.... The following is a replacement for the second part of posting #8.

Refer to the attached image and Excel file.

For a loan with a balloon payment, the regular payment is calculated based on a fully-amortized loan (i.e. no balloon payment) over some longer term (*).

Typically, a 30-year term (360 months) term is used (*).

But in your case, the "remaining amortization" term in B3 (240 months) is used. The formula in F7 is:

=ROUND(PMT(F2/12, B3, -B2), 2)

That results in 64,200.68.

That regular payment is applied to a loan of a shorter term; 132 months, in your case.

Thus, the smaller regular payment results in a last "balloon payment".

(*) I do not see the need for such a rigorous approach, unless contractually, the borrower is given the option of extending the loan term to the longer period that the regular payment is based upon. Otherwise, IMHO, the regular payment for the balloon loan can be any arbitrary amount that is "sufficiently" small. IMHO, it would be more practical for the lender or borrower to choose a required balloon payment, then calculate the regular payment that has that result. The formula for that is
=ROUND(PMT(F2/12, B6*12-1, -B2, F4/(1+F2/12)), 2), where F4 is the "balloon payment" or "total last payment", depending on the definition of "balloon payment" that the lender chooses (see below).

-----

The more significant issue is how the term "balloon payment" is defined.

Apparently, there are two definitions that are widely used in the financial industry.

But as you will see, the difference is just a matter of semantics.


Method 1: Your bank defines "balloon payment" as the entire last payment (larger than the regular payment).

That is, it is the second-to-last balance plus interest. Thus, the final balance is zero.

bankrate.com uses this definition of "balloon payment", for example.

This balloon payment can be estimated by the formula in F9, to wit:

=ROUND(FV(F2/12, B6*12-1, F6, -B2)*(1 + F2/12), 2)

or more simply:

=ROUND(FV(F2/12, B6*12, F6, -B2) + F6, 2)

In your case, the actual balloon payment in F10 differs slightly because interest is rounded monthly in the amortization schedule.

The following formula in F10 finds the last payment in the amortization schedule in columns H:L.

=LOOKUP(1E+300, J4:J136)

The result is 5,933,802.43

-----

Method 2: Alternatively, the "balloon payment" is the final balance after the last regular payment.

Thus, the "total last payment" is the last regular payment plus the "balloon payment".

I learned this definition of "balloon payment" from the HP-12C calculator (c. 1981), which used to be the "gold standard" for the financial industry.

Investopedia and rocketmortgage.com also use this definition of "balloon payment", for example.

This balloon payment (final balance) can be estimated by the formula in F13, to wit:

=ROUND(FV(F2/12, B6*12, F6, -B2), 2)

The estimated total last payment in F14 is then:

=ROUND(F6+F13, 2)

Again, the actual balloon payment (final balance) in F15 and the total last payment in F16 differ slightly because interest is rounded monthly in the amortization schedule.

The following formula in F15 finds the actual balloon payment in the amortization schedule in columns N:R.

=LOOKUP(1E+300, R4:R136)

The result is 5,869,601.75

And the actual total last payment in F16 is calculate as follows:

=ROUND(F15+F6, 2)

The result is 5,933,802.43, which is the same as the "balloon payment" for Method 1.


-----

[curiouscat408]

Okay, I took a stab at trying to apply the formulas that I provided to your "sample" Excel file.

Refer to the attached image and Excel file.

Study the formulas in the yellow-highlight cells.

As I noted previously, I am not sure I know what you are trying to do without an explanation.

The "Loan" section (data in A1:B12 and amortization schedule in E3:L162 in my file) is straight-forwared.

But for the "Refinance" section (A14:B25 and N3:T162), I developed my own scenario. I hope it is close to your intent.

The refinance scenario assumes that we refinance the current outstanding balance (B15) starting on the first of next month.

The terms of the refinance loan is similar to the original loan (probably not realistic), to wit: a term of 156 months (B21), with an interest-only term of 2 years (B19).

The key difference is: the regular payment of the refinanced loan (B23) is based a fully-amortized loan over 25 years (B16).

(As I mentioned before: I do not understand why the industry chooses to structure a balloon loan that way. We can choose any arbitrary regular payment, based on the lender's and borrower's requirements. At the very least, we can round the regular payments to 45,339 or 45,340, resulting in little difference in the balloon payment.)

-----

The following are some important points to understand.


1. Do not confuse payments on the "first of the month" with payments "at the beginning of the period" (type=1 in Excel functions).

The bank treats the first-of-the-month payments as the "at the end of the period" (type=0 in Excel functions).

This is self-evident from the interest for period #25 in the bank's amortization schedule.

It is 34,200.00 = 10,800,000*3.78%/12 -- one month interest on the previous-month outstanding balance.

If payments were truly "at the beginning of the period", the interest for period #25 would be 33,817.77 = (10,800,000 - 64,200.68)*3.78%/12.


2. Note, again, the difference between the estimated ballon payment (B11) and the actual balloon payment (B12).

The difference is due to rounding interest each month in the amortization schedule.

No Excel formula can account for periodic rounding.

Usually, I do not round the periodic calculations in an amortization schedule, for that very reason.


-----

[raycal84]

Okay, I took a stab at trying to apply the formulas that I provided to your "sample" Excel file.

Refer to the attached image and Excel file.

Study the formulas in the yellow-highlight cells.

As I noted previously, I am not sure I know what you are trying to do without an explanation.

The "Loan" section (data in A1:B12 and amortization schedule in E3:L162 in my file) is straight-forwared.

But for the "Refinance" section (A14:B25 and N3:T162), I developed my own scenario. I hope it is close to your intent.

The refinance scenario assumes that we refinance the current outstanding balance (B15) starting on the first of next month.

The terms of the refinance loan is similar to the original loan (probably not realistic), to wit: a term of 156 months (B21), with an interest-only term of 2 years (B19).

The key difference is: the regular payment of the refinanced loan (B23) is based a fully-amortized loan over 25 years (B16).

(As I mentioned before: I do not understand why the industry chooses to structure a balloon loan that way. We can choose any arbitrary regular payment, based on the lender's and borrower's requirements. At the very least, we can round the regular payments to 45,339 or 45,340, resulting in little difference in the balloon payment.)

-----

The following are some important points to understand.


1. Do not confuse payments on the "first of the month" with payments "at the beginning of the period" (type=1 in Excel functions).

The bank treats the first-of-the-month payments as the "at the end of the period" (type=0 in Excel functions).

This is self-evident from the interest for period #25 in the bank's amortization schedule.

It is 34,200.00 = 10,800,000*3.78%/12 -- one month interest on the previous-month outstanding balance.

If payments were truly "at the beginning of the period", the interest for period #25 would be 33,817.77 = (10,800,000 - 64,200.68)*3.78%/12.


2. Note, again, the difference between the estimated ballon payment (B11) and the actual balloon payment (B12).

The difference is due to rounding interest each month in the amortization schedule.

No Excel formula can account for periodic rounding.

Usually, I do not round the periodic calculations in an amortization schedule, for that very reason.


-----

Thanks again! This has been so helpful! I will look at this more in depth today.