Loan Amortization Table

Hi all,

So I tried to find amortization tables on the web for real estate but most of them do not account for loan curtailment (early prepayment) so I was wondering if anyone could provide guidance on how a proper one is made. Also, in terms of amortization per year, is it just 1 divided by the amortization period and multiplied by the principal outstanding? For example:

Say loan is $500,000 and the amortization period is 30 years.. do you just apply (1/30%) = 3.33% to $500,000 to get the amount amortized?

Attachment Size
amortization_table.xls 56.5 KB 56.5 KB
 
Best Response

Yes, there are tables on the web that do it by periods, so you can look for the prepay. I am assuming you mean as an example a 10-year loan balloon that has a 30-year amortization, like a standard commercial loan. Don't calculate it the way you wrote, that is just wrong. Maybe look up what a loan constant is to understand why it is wrong.

The easiest way is to set up a vertical table with following rows:(Period,BeginningOfPeriod,Principal Payment, Interest Payment, EndOfPeriod). Always calculate in months! Then below the set of rows put period 1, then for the below period 2(month two), etc, etc. Then at period 1,BOP is the total loan amount. The row to thr right of that is principal so use the ppmt function(remember interest rate divided by 12, in the formula) e.g. i/12 not .03%. make sure to hard code($) the BOP reference cell instead of just inputting the value of the principal. Then interest rate is row is principal*rate/12. The EOP row should be your princiapl balance at the end of 1 month, which is what you are asking for. To input this into the cell simply refernce the cells for the Principal (BOP) and the principal payment cell(Principal). Then make the BOP for period 2(below) equal to EOP of period 1 and you are set! Just copy and drag all the values down until you get to the month you want, that is your amortization table.

Please excuse the spelling, way too tired to fix it.

 

Short answer, always calculate in the exact finance terms . They usually happen to be in months

I was referring to months as opposed to years, then divided by months. If you just use years, and yearly interest rate then your debt payment(P&I) will not be the same as if it were calculated as Interest rate divided by months(12). You have to understand the difference between nominal rate and effective rate. The effective rate(monthly rate*12) while quoted in years will always be a little less than the nominal rate quoted in years because you are paying down principal).

 

Hey guys.. I very much appreciate your replies and have a few follow up questions. So I made an amortization table spread sheet that I attached (to the original post at the beginning of this thread):

  1. So when I mentioned early prepay, I had meant loan curtailment. So if I agreed to a monthly amortization and monthly interest payment, but I have additional cash flow left after the debt service and I want to use it to pay off the principal (in addition to the already paid amortization), how does this get factored into the amortization table? I tried doing it in the attached but it doesn't seem to make as much sense because the payment stays constant (cell C15), but interest is going down as I curtail more principal.. so shouldn't the payment constant be going down as well?

  2. Second, how do you account for floating rate loans when calculating the loan constant payment? it asks for an interest rate in the PMT formula, but doesn't the interest rate change over time? and if taking rate at period 0, do you take the current LIBOR or the LIBOR floor?

  3. I tried to show both annualized and monthly tables, but for some reason, the payment constant using the PMT formula on an annual basis (cell C16) is not equaling the payment constant using the PMT formula on a monthly basis multiplied by 12 (cell D15). Do you know why this is the case?

  4. Following up to the above question (3), interest and amortization per year when adding up the monthly table does not equal interest or amortization per year in the annual table

  5. Last question.. are most acquisition models done on a monthly basis or a annual basis? if annual, you just sum up the interest, amortization, and curtailment in a monthly amortization table when calculating leveraged cash flows?

Sorry for all the questions! I just want to make sure I get this right, and thought some experienced members of the forum would be able to assist me! Thank you once again!!

 

You would have to recast the payment each month using the new UPB to make the loan constant change with the partial pay downs each month.

As a side note: in the U.S., there are very few, if any, lenders that will allow for a monthly partial pay down like you are describing. Beyond the fact that it is an accounting nightmare, most lenders use average life calculations to figure out the duration of their investment and don't like it screwed with. If they even allow it, know that you will be charged fees...a lot of them.

If you want to put more towards principal each month, just change the amortization of the loan. Most lenders would be more than happy to accept a 27 or 25 year amortization vs. a 30 year if that is what you want.

Every floating rate loan I've ever done uses a set rate to calculate principal amortization. These rates are negotiable, but we are underwriting using a 5%-5.5% rate right now.

For question 3/4: The reason this is happening is because of the monthly compounding. A 4.0% MEY is about a 4.07% BEY. (1-(.04/12))^12-1

Every model I've ever seen/completed calculates on a monthly basis and then adds everything together on an annual basis to match your cash flow.

 

Mrcheese - this was very very helpful. I just wanted to know more about the point about 5%-5.5% amortization. Can you provide more detail on what that rate is applied to? From my understanding, in amortization tables, you use the PMT formula to calculate the monthly payment. Then the interest payment based on the average on BOP and EOP balance is subtracted from the monthly payment to get the amortization payment. So in a floating rate loan, does the monthly payment stay constant or change over time as interest goes up? Also, if the initial payment is based on a L+Spread with a LIBOR floor, should the initial rate be floor+spread or current LIBOR+ spread.

Also at the end when you say monthly calcs and annual cash flows.. So most real estate models are calculated on an annual basis (down to leveraged cash flows) and then the amortization table is calculated on a monthly basis and summed up to get annual amounts of interest and amortization?

Thanks again for all your help!

 

In a floating rate deal, the monthly payment changes. However, most lenders want steady, predictable principal amortization of the loan. To satisfy this, they basically pick an interest rate and then emulate the principal reduction from that schedule. We use 5.0% or 5.5% as the interest rate to emulate in our underwriting.

Using the $1 million loan amount and a 5% rate, the principal reduction would be $1201.55, $1206.56, $1211.58 and so on.

The first month's payment would be $1201.55 + the interest calculated on the $1MM based on the floating rate, the second month's payment would be $1206.55 + calculated interest on $998798.45, and so on.

Yes, most deals are presented as 10 year annual cash flows. The debt service payments are calculated on a monthly basis, but aggregated on a yearly basis so they match up to the 10 year CF.

 

Qui sed asperiores harum nisi asperiores quia est ipsa. Ea omnis ut qui nisi officiis aut rerum. Natus autem eveniet veniam voluptatem. Quia ea ad vel et. Eos ut voluptatem debitis corrupti. Ut sed consequatur illo molestiae eveniet et. Ea tenetur sit nostrum voluptate excepturi.

Hic consectetur iusto tempore maxime impedit fugit exercitationem. Sint harum dicta rerum et. Tenetur accusamus omnis voluptates eius unde atque sit.

Vero architecto adipisci dolor voluptatum. Aut minus qui eum est natus sit. Ad deserunt ullam quo. Ut minima fugiat qui corporis.

 

Voluptas delectus reprehenderit non cum incidunt inventore. Dolores eum et quam assumenda dicta enim minus. Ipsa tempore voluptates sed doloribus praesentium. Voluptate eos cupiditate ut.

Nemo corrupti aut harum asperiores odit nobis. Ut totam itaque consequuntur maiores qui nemo.

Aut magni qui adipisci voluptatem. Laudantium incidunt nesciunt quia cupiditate et. Quia quaerat vel voluptates laboriosam sequi et voluptates. Adipisci earum sequi ratione quas consectetur. Labore quo est laborum voluptas rerum. Itaque nisi minus minus ut doloribus.

In id placeat amet ipsum nobis minus. Saepe dolorum facilis quasi voluptas quisquam.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”