(OLD) How to Build a Dynamic Amortization Table in Excel (OLD)

Поделиться
HTML-код
  • Опубликовано: 4 ноя 2024

Комментарии • 50

  • @jessicaamir7433
    @jessicaamir7433 6 лет назад +1

    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.

  • @pauljames6478
    @pauljames6478 7 лет назад

    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!

  • @HerreCutt
    @HerreCutt 8 лет назад +1

    I'm trying to get into real estate and this will help me find out immensely! Thank you!

  • @pv0315
    @pv0315 6 лет назад

    Very stylish presentation. Thumbs up.👌

  • @josephpgarrido
    @josephpgarrido 8 лет назад

    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.

  • @alfonsogu9
    @alfonsogu9 8 лет назад +3

    Hi Spencer. Love the tutorial video, I wanted to ask you can create an amortization schedule using variable interest rates. Thanks!

  • @roonii334
    @roonii334 3 года назад

    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.

  • @DavidJames0322
    @DavidJames0322 8 лет назад

    Hey Spencer Burton great job, I learnt a lot from this 1 video.

  • @drpraveenkumark6240
    @drpraveenkumark6240 Год назад

    Thank you 🌹

  • @MSOfficeLearners
    @MSOfficeLearners 4 года назад

    nice work

  • @Raptor3Falcon
    @Raptor3Falcon 8 лет назад

    Thanks Spencer Tracy!" :D It was really helpful!

  • @Justin508
    @Justin508 8 лет назад

    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.

  • @meelan1zxcvbnm
    @meelan1zxcvbnm 7 лет назад

    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!

  • @markd.quinlan9819
    @markd.quinlan9819 6 лет назад

    How would I modify this schedule to show an interest-only grace period for the first year of the loan?

  • @photoshop-solutions-cs6176
    @photoshop-solutions-cs6176 6 лет назад

    is there a way to add a dynamic beginning and ending date?

  • @hassanqadir9423
    @hassanqadir9423 7 лет назад

    Is it possible to create this Loan Amortization table through VBA?

  • @Justin508
    @Justin508 8 лет назад

    My apologies for blabbing but, when I change the term my Payoff @ year ___ ends up with a REF error. Any thoughts?

  • @johntrolle8935
    @johntrolle8935 4 года назад

    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?

    • @adventuresincre
      @adventuresincre  4 года назад

      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.

    • @johntrolle8935
      @johntrolle8935 4 года назад

      @@adventuresincre Got it. Many thanks.

  • @abujamaal
    @abujamaal 7 лет назад

    DO you have one with variable interest rates ?

  • @jasond.8093
    @jasond.8093 8 лет назад

    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.

    • @adventuresincre
      @adventuresincre  8 лет назад

      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!

    • @jasond.8093
      @jasond.8093 8 лет назад

      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?

    • @jasond.8093
      @jasond.8093 8 лет назад

      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

  • @dg092163
    @dg092163 7 лет назад

    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!

    • @bkolodny
      @bkolodny 6 лет назад

      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.

  • @Le_Kinho
    @Le_Kinho 6 лет назад

    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?

  • @frankyd88
    @frankyd88 8 лет назад +1

    I get problems when i add a curtailment value? Can you advise?

    • @adventuresincre
      @adventuresincre  8 лет назад

      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!

  • @hassanqadir9423
    @hassanqadir9423 7 лет назад

    Mr Spencer, can you tell me where can I find the updated file that is free from errors.

    • @adventuresincre
      @adventuresincre  7 лет назад

      Hassan Qadir You can find the model here: www.adventuresincre.com/excel-mortgage-amortization-table-model/

    • @hassanqadir9423
      @hassanqadir9423 7 лет назад

      yes I found it. Thankyou.

  • @aecandia
    @aecandia 8 лет назад

    i tried to fallow your steps, but i get the value error.. ;(

  • @marcust.williams7210
    @marcust.williams7210 6 лет назад

    Hey Spencer,
    How can I contact you?

  • @TheZuOmurbek
    @TheZuOmurbek 8 лет назад

    can you please share how you copy down at 24:18?

    • @adventuresincre
      @adventuresincre  8 лет назад

      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/

  • @jacquelinevictoriateer650
    @jacquelinevictoriateer650 6 лет назад

    This model breaks down with a fully i/o loan structure :(

    • @adventuresincre
      @adventuresincre  6 лет назад +1

      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.

  • @niclandstrom
    @niclandstrom 5 лет назад

    i want to buy you a new mic, Spence

    • @adventuresincre
      @adventuresincre  5 лет назад

      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.

    • @niclandstrom
      @niclandstrom 5 лет назад

      @@adventuresincre Blew me away with the reply! I watch all your videos. Very helpful. Thanks you legend.

  • @davidsiebert1454
    @davidsiebert1454 7 лет назад

    you like to digress! get to the point instead of all the darn details!

    • @meganmills6545
      @meganmills6545 6 лет назад

      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...