Extreeeeeemly helpful. Awesome video. Thank you so much. Spencer. I"m doing my Masters of Applied Finance (Investment Management) in Australia and this is THE best thing I have seen. I love how thorough you are. Far surpasses any lecture. I have learnt SO much for this one video. Brilliant stuff Mr Burton. Jess.
Spencer - fantastic tool that you have created. It is exactly what I was looking for and despite many years working in Excel learned several tips and tricks. Thanks!
Works great! ive adapted this to my payment schedule on my car. Ive also modified curtailment to a custom dollar amount each month. It wasn't until recently that ive started larger payments.
Hi Spencer, I loved the tutorial videos. I want to ask you if i want to make a debt schedule that test the project NOI ability to pay the debt interest & principle payments, how can i do that? Thank you very much.
This is very helpful. Would it be possible to incorporate grace period in the table, where we have both interest only payment during the grace period and the other one to have no interest and no principle, but accrued during the non payment amount and the payment begin at the end of the grace period say for 2 or 3 or 4 years? Appreciate your help!
First, great video. This is going to help with my Real Estate investments course this semester! However, My ending balance on period 120 equals my payoff amount rather than being 0. Any ideas? I know it's gotta be in my formula somewhere but I'm unable to see it at the moment. Thank you. Keep up the good work.
The spreadsheet doesn't take curtailment into account when calculating the payoff after term. Curtailment creates a negative ending balance and regardless of contribution, it will continue to calculate for the duration of the amortization and its respective term.
Hi Jason, thanks for spotting the error. I've updated the file so that the payoff is now correctly calculated when curtailment payments are included. Thanks!
IDK... The way you have it now, it still errors under the curtailment within the table while figuring a full term loan... Using your spreadsheet, put your curtailment to $1,000,000 and the term to 30 years. See how payoff at the end of year 30 = -$828,729,240 and the lender yield is 88.21%? Try changing formulas in the following cells to: B15: =IFS(H14="","",H14=0,"",H14>0,B14+1,Payoff*Periods>B14,B14+1) F15: =IFS(C150,Curtailment) Now drag those formulas down. Using the same variables as above, it should show the last curtailment on week 15 as $276,123 (because there's no way you'll overpay that last payment) with a $0 ending balance and the Lender Yield is 8.22%. Everything below week 15 should be blank. Now drop the term to 0.5 years and you'll get an $8,867,289 payoff (shows under Principal as well)... The lender yield will show 9.95%. Is that working properly?
That errors in the Principal formula and won't modify the curtailment if the beginning balance is less than it. Switch to: E15: =IFS(B15=Periods*Payoff,C15-Curtailment,B15="","",C150,Payment-D15) F15: =IFS(B15="","",C15-E15
How can the term be shorter than the amortization period. If you have a baloon pmt and you pay off your balance there is nothing left to amortize. What am i missing here?
Hi John. The term is the length of the loan, whereas the amortization is the period over which the loan amortizes down to zero. Thus, the amortization period helps determine the payment amount. So if a loan has a 10 year term and 30 year amortization, that means at the end of the 10-yr term the loan will have a balloon balance equal to 20 years of remaining amortization.
Hi, I've corrected an error where the payoff amount wasn't calculating correctly when including curtailment payments. The files has been updated to v1.1. Thanks!
I owner financed a property to someone for 10 years, $70,000, at 8%. Very simple, right? Well, she has made some late payments, partial payments and double payments. The contract also calls for a 5% late fee for any payments 10 days late. Do you have any suggestions on how to do this? Thanks so much!
Deanna Gearinger I have a similar situation and am looking for a amortization program that also handles prepayments, late payments, multi payments in a month, and late payment interest.
Mr. Burton, I keep trying to fast-track to the last non-empty cell with control+down arrow but it brings me all the way down to cell 373 since those all have formulas. How does one go to the last relevant period (I.e. 120) quickly without always being brought to cell 373 and having to go back up?
You bet! We created a free Excel add-in that adds numerous keyboard shortcuts, including the ability to copy down. It's called 'Excel 4 CRE': www.adventuresincre.com/custom-excel-addin-for-real-estate/
Thanks Man! And you're right. Thankfully I've purchased a new mic since recording this in 2015. But maybe it's time to update this video! I'll put that on my to do list for this year.
Are you joking? I hope so because many of us learn from those digressions! And it's rude to look a gift horse in the mouth. If you don't like his delivery style there's plenty else for you to watch. But I digress...
Extreeeeeemly helpful. Awesome video. Thank you so much. Spencer. I"m doing my Masters of Applied Finance (Investment Management) in Australia and this is THE best thing I have seen. I love how thorough you are. Far surpasses any lecture. I have learnt SO much for this one video. Brilliant stuff Mr Burton. Jess.
I'm trying to get into real estate and this will help me find out immensely! Thank you!
Hi Spencer. Love the tutorial video, I wanted to ask you can create an amortization schedule using variable interest rates. Thanks!
Spencer - fantastic tool that you have created. It is exactly what I was looking for and despite many years working in Excel learned several tips and tricks. Thanks!
Works great! ive adapted this to my payment schedule on my car. Ive also modified curtailment to a custom dollar amount each month. It wasn't until recently that ive started larger payments.
Great to hear you found it useful!
Hi Spencer, I loved the tutorial videos. I want to ask you if i want to make a debt schedule that test the project NOI ability to pay the debt interest & principle payments, how can i do that?
Thank you very much.
Very stylish presentation. Thumbs up.👌
Hey Spencer Burton great job, I learnt a lot from this 1 video.
This is very helpful. Would it be possible to incorporate grace period in the table, where we have both interest only payment during the grace period and the other one to have no interest and no principle, but accrued during the non payment amount and the payment begin at the end of the grace period say for 2 or 3 or 4 years? Appreciate your help!
First, great video. This is going to help with my Real Estate investments course this semester! However, My ending balance on period 120 equals my payoff amount rather than being 0. Any ideas? I know it's gotta be in my formula somewhere but I'm unable to see it at the moment. Thank you. Keep up the good work.
How would I modify this schedule to show an interest-only grace period for the first year of the loan?
is there a way to add a dynamic beginning and ending date?
The spreadsheet doesn't take curtailment into account when calculating the payoff after term. Curtailment creates a negative ending balance and regardless of contribution, it will continue to calculate for the duration of the amortization and its respective term.
Hi Jason, thanks for spotting the error. I've updated the file so that the payoff is now correctly calculated when curtailment payments are included. Thanks!
IDK... The way you have it now, it still errors under the curtailment within the table while figuring a full term loan... Using your spreadsheet, put your curtailment to $1,000,000 and the term to 30 years. See how payoff at the end of year 30 = -$828,729,240 and the lender yield is 88.21%?
Try changing formulas in the following cells to:
B15: =IFS(H14="","",H14=0,"",H14>0,B14+1,Payoff*Periods>B14,B14+1)
F15: =IFS(C150,Curtailment)
Now drag those formulas down.
Using the same variables as above, it should show the last curtailment on week 15 as $276,123 (because there's no way you'll overpay that last payment) with a $0 ending balance and the Lender Yield is 8.22%. Everything below week 15 should be blank.
Now drop the term to 0.5 years and you'll get an $8,867,289 payoff (shows under Principal as well)... The lender yield will show 9.95%.
Is that working properly?
That errors in the Principal formula and won't modify the curtailment if the beginning balance is less than it. Switch to:
E15: =IFS(B15=Periods*Payoff,C15-Curtailment,B15="","",C150,Payment-D15)
F15: =IFS(B15="","",C15-E15
Thank you 🌹
How can the term be shorter than the amortization period. If you have a baloon pmt and you pay off your balance there is nothing left to amortize. What am i missing here?
Hi John. The term is the length of the loan, whereas the amortization is the period over which the loan amortizes down to zero. Thus, the amortization period helps determine the payment amount.
So if a loan has a 10 year term and 30 year amortization, that means at the end of the 10-yr term the loan will have a balloon balance equal to 20 years of remaining amortization.
@@adventuresincre Got it. Many thanks.
nice work
Is it possible to create this Loan Amortization table through VBA?
DO you have one with variable interest rates ?
My apologies for blabbing but, when I change the term my Payoff @ year ___ ends up with a REF error. Any thoughts?
Thanks Spencer Tracy!" :D It was really helpful!
I get problems when i add a curtailment value? Can you advise?
Hi, I've corrected an error where the payoff amount wasn't calculating correctly when including curtailment payments. The files has been updated to v1.1. Thanks!
I owner financed a property to someone for 10 years, $70,000, at 8%. Very simple, right? Well, she has made some late payments, partial payments and double payments. The contract also calls for a 5% late fee for any payments 10 days late. Do you have any suggestions on how to do this? Thanks so much!
Deanna Gearinger I have a similar situation and am looking for a amortization program that also handles prepayments, late payments, multi payments in a month, and late payment interest.
Mr. Burton, I keep trying to fast-track to the last non-empty cell with control+down arrow but it brings me all the way down to cell 373 since those all have formulas. How does one go to the last relevant period (I.e. 120) quickly without always being brought to cell 373 and having to go back up?
i tried to fallow your steps, but i get the value error.. ;(
Mr Spencer, can you tell me where can I find the updated file that is free from errors.
Hassan Qadir You can find the model here: www.adventuresincre.com/excel-mortgage-amortization-table-model/
yes I found it. Thankyou.
can you please share how you copy down at 24:18?
You bet! We created a free Excel add-in that adds numerous keyboard shortcuts, including the ability to copy down. It's called 'Excel 4 CRE': www.adventuresincre.com/custom-excel-addin-for-real-estate/
Hey Spencer,
How can I contact you?
This model breaks down with a fully i/o loan structure :(
I hadn't noticed, thanks for pointing it out - of course, not really any reason to create an amortization schedule when the loan is all IO.
i want to buy you a new mic, Spence
Thanks Man! And you're right. Thankfully I've purchased a new mic since recording this in 2015. But maybe it's time to update this video! I'll put that on my to do list for this year.
@@adventuresincre Blew me away with the reply! I watch all your videos. Very helpful. Thanks you legend.
you like to digress! get to the point instead of all the darn details!
Are you joking? I hope so because many of us learn from those digressions! And it's rude to look a gift horse in the mouth. If you don't like his delivery style there's plenty else for you to watch. But I digress...