How to Create a Loan Amortization Table in Excel

[@RyanOConnellCFA]

💾 Download Free Excel File: ► Grab the file from this video here: ryanoconnellfinance.com/product/loan-amortization-table-template-in-excel/ 🔑 Join this channel to get access to perks & support my work: kzbin.info/door/Akyj2N9kd0HtKhCrejsYWQjoin

[@giles5493]

You're a life saver these videos are just the right length with all the detail needed

[@RyanOConnellCFA]

Thank you, I appreciate it!

[@simrandhanoa5770]

Thank you so much Ryan ! I watched a few of these videos but for some reason, the drop-down wouldn't work, your method works. Simple and quick.

[@RyanOConnellCFA]

You're welcome and I'm glad it helped!

[@LexJoeHome]

Thank you for the video! I'm an accounting student using it for a class project. Should the PMT type be set to 1 because the payments are due at the beginning of the period, making it an annuity due?

[@85MA]

Could you please create tutorials on Interest-Only Loan Amortization Table and Variable Rate Amortization Table? It would be great if you could include options for payment periods such as monthly, annually, and semi-annually in a dropdown list. I appreciate your simple yet comprehensive teaching style. If possible, I would be grateful if you could upload these videos. Thank you in advance.

[@RyanOConnellCFA]

Thank you for the suggestion, I can look into making more complex loan amortization table videos in the future!

[@bhavneshparikh8283]

Can be possible client wise auto update loan amotozation table? Also if possible interest rate change so auto update automatic in excel Extra Payments means (Start at Payment No,Extra Payment,Payment Interval,Extra Annual Payment,Payment,Total Extra Payments) Additional Payment already showing in your video ,Variable or Fixed Rate ,Impact of interest rate HIKE on your loan EMI & repayment schedule & Impact of interest rate CUT on your loan EMI & repayment schedule ? how to create in excel & Suppose provide only interest

[@mutalekasuba54]

excellent video. Very helpful

[@RyanOConnellCFA]

Thank you!

[@ra4872]

How to calculate interest rate if i pay principal quarterly and interest monthly on a 12 month period loan? thanks

[@RyanOConnellCFA]

For a 1 year loan with quarterly interest payments the number of periods would only be 4. You would need to use a quarterly rate rather than a monthly rate. For a 1 year loan with monthly interest the number of periods would only be 12

[@rawlishaanatol-sylvester6285]

How can i account for an extra payment made in this schedule??

[@RyanOConnellCFA]

To account for an extra payment in a loan amortization schedule in Excel, you can adjust the balance calculation to subtract the amount of the extra payment in the period it was made. This will reduce the remaining balance and consequently alter the interest and principal amounts in subsequent periods.

[@pizzaandmeth4538]

Crazy,again just as I got past the loan part in FRA you make this video..You reading my mind bro?hahah

[@RyanOConnellCFA]

Lol, now I'm going to have to start predicting your schedule ahead of time! Let me guess, equities next?

[@pizzaandmeth4538]

@@RyanOConnellCFA Well on corporate finance now and was planning equities next,I'm sure by the time your done with the video I'll be there 😂

[@RyanOConnellCFA]

@@pizzaandmeth4538 Haha good luck! I really haven't decided what topic to tackle next yet

[@pizzaandmeth4538]

@@RyanOConnellCFA Great content either way,I enjoy CFA content being taught on excel like your doing.

[@RyanOConnellCFA]

@@pizzaandmeth4538 Thank you, I really appreciate it! I've found a lot of the stuff is a lot easier to show and visualize in Excel and the videos in Excel seem to do well in terms of views too

[@mutalekasuba54]

how do you include time at start and the end of the period as well as graphs?

[@RyanOConnellCFA]

Could you please add more detail to what you mean by this question?

[@gomezchris276]

Man I haven’t done an amortization table in years, feels weird to refresh my memory on this

[@RyanOConnellCFA]

Haha I felt the exact same way making this video! But it really is a lot like riding a bike

[@mutalekasuba54]

thank you.

[@RyanOConnellCFA]

You're welcome!

[@sayednab]

the excel file is totally different than the file you are showing on your video?

[@RyanOConnellCFA]

Thank you so much for alerting me of this issue. The free download link is fixed now and is the file shown in the video

[@sayednab]

@@RyanOConnellCFA thanks Ryan! I was wondering, how do you make amortization with variable rates which changes on monthly basis. to be more precise, if it follows the LIBOR rate.?

[@mochallo]

why i my excel did not appear the values when drag it down 😭it appear the weird value, not the answer

[@RyanOConnellCFA]

What is the value you got? Does it say #N/A?

[@Xtrails206]

Why isn't mine having 0 ending balance

[@RyanOConnellCFA]

You must have an incorrect formula somewhere a long the way. You should go back and double check each one to ensure that it is accurate

[@tanweerabbasabbas649]

thk u

[@RyanOConnellCFA]

My pleasure