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.
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
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.
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.
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.
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!
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!
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.
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!
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.
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.
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?
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...
Great question! We created a free Excel add-in that adds numerous keyboard shortcuts, including the ability to automatically turn cell font blue. It's called 'Excel 4 CRE': www.adventuresincre.com/custom-excel-addin-for-real-estate/
Hi Spencer. Love the tutorial video, I wanted to ask you can create an amortization schedule using variable interest rates. Thanks!
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.
Is it possible to create this Loan Amortization table through VBA?
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
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.
Can this be used for raising capital from a private lender
DO you have one with variable interest rates ?
I'm trying to get into real estate and this will help me find out immensely! 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
Thank you 🌹
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.
My apologies for blabbing but, when I change the term my Payoff @ year ___ ends up with a REF error. Any thoughts?
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!
How would I modify this schedule to show an interest-only grace period for the first year of the loan?
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!
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.
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!
Very stylish presentation. Thumbs up.👌
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!
Hey Spencer Burton great job, I learnt a lot from this 1 video.
i tried to fallow your steps, but i get the value error.. ;(
is there a way to add a dynamic beginning and ending date?
Hey Spencer,
How can I contact you?
Thanks Spencer Tracy!" :D It was really helpful!
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.
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/
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.
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 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...
At 7:28 how did you change to the color blue so quick?
Great question! We created a free Excel add-in that adds numerous keyboard shortcuts, including the ability to automatically turn cell font blue. It's called 'Excel 4 CRE': www.adventuresincre.com/custom-excel-addin-for-real-estate/