Watch Me Build a Fully Dynamic Mortgage Amortization Table in Excel
Вставка
- Опубліковано 30 чер 2024
- Watch as a build a fully dynamic, mortgage amortization in Excel. Find the tutorial and files here: www.adventuresincre.com/excel...
00:00 - Introduction
01:08 - Assumptions
07:20 - Custom Rows
08:45 - Beginning Balance
10:21 - Payment
12:26 - Curtailment Payments
15:39 - Interest
17:01 - Principal
18:12 - Payoff and Lender Yield Explanation
Check out our other Watch Me Build videos here: www.adventuresincre.com/re-ed...
Join the A.CRE Real Estate Financial Modeling Accelerator and learn to build institutional-quality real estate models from scratch: www.adventuresincre.com/accel...
Incredibly helpful!
Super helpful especially with being able to calculate the interest only period. Thank you!
Many Thanks to the Legend. 🙏😊
Thank you, it was very useful
Hi. Thanks for this video it was very helpful, can you please show me how can I also add a column for Payments date to calculate interest on the actual days of each month for monthly payments and keep it dynamic too
Do you have a video that shows how to make the cash flow portion of the model dynamic as well? (i.e. the debt service column automatically sums annual payments based on cash flow year and date of IO/AMO expiration?) Would be greatly appreciated. Thank you!
how is it done with variable rates and if there is a monthly transaction fee?
Hi Mr spencer i have used all your instruction related to dynamic amortisation table but during copying the formula it only compute to period 2 and loan become zero
In that case you have to check the formulas, you probably haven't locked the cells term, period etc. They are fixed in the formula
Everything is dead on except when I copy the fields, I hit control C went down then hit alt HVF and I get all "value". please advise
Hi, I'd like to thank you for your contribution to our knowledge in Real Estate financial modeling. However, there is something I don't understand about your spreadsheet... At min 23:13, when you change curtailment to $50,000... column F (the one that tracks principal payments) shows a payment of $208 which shouldn't be there since we are still in the interest only period? Or could I be misunderstanding something?
In order to fix that issue I've used Boolean logic for the principal payment formula (cell F16) as follows: =+IF(B16="","", (B16>(IO_years*Periods))*( (Curtailment=0)*(D16-E16)+(Curtailment>0)*PPMT(Rate/Periods,B16,Amortization*Periods,-Loan_Amount) )). However, I am not sure how curtailment affects the amortization of the loan.... I am assuming curtailment does not affect the amortization of the loan's principal, which is the reason why I've used PPMT function. As a result of this, the debt service will not be constant over the periods (which I am not sure whether that's correct or not)
Thanks again for your free educational material. Looking forward to hearing from you soon!
Best regards,
Victor
Victor Ly
Hi, I agree with you when you say there should be no principal amortization at that time since we are in the interest only period, which there should be no principal amortization other than the curtailment paid by the borrower.
However, your formula doesn’t really fit in because each time there is a curtailment the loan should technically recalculate itself into a French amortization as if the principal were lower or more typically, curtailments are allocated in reverse order, minimizing the amount of the final balloon or final payments (in which case, the French amortization would also need to be recalculated because interest payments would change since we now have less principal)
Hi Victor, that is as it should be. The curtailment payments reduce the loan balance throughout the interest-only period. Therefore the consistent interest-only payments more than cover the actual interest due and start to pay down the principal.
It seems your question may well be "Well, shouldn't the interest-only payments start to decrease as well, to reflect the actual interest on the balance?" And for that question, I do not have an answer...
I used this Formula IF(B16="","",IF(B16>(IO_Years*Periods),D16-E16,0)) and it worked fine. Just wanted to know why did you use such a long formula? Any logic behind this?
@@hardwick1010 Hi James. I disagree; There is actually an error in the video in the form of the Principal's formula being too simple. The formula Spencer wrote simply deducted interest from the total payment owed in the period, and left a blank if the period was blank. What he should've done was include a part of the formula where Principal is only calculated if the period of the row is after the IO term.
I agree that curtailment decreases the balance of the loan, but if the model was truly dynamic, the total payment, interest payment, and principal payment calculations would have adjusted for the new balance for each period within the IO term.
I love Spencer's videos, and I think this is just a little oversight on his part. I'm sure he will correct it in a future video. @SpencerBurton
I used the formula =if(B16="","",if(B16
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
If you are paying a curtailment of $50K for the 3 years of Interest Only shouldn't that interest payment be going down as well? =IF(B16"","",MIN(C16+E16,(B16(E10*E7))*E11)).