Mortgage Calculator: A Simple Tutorial (template included)!
About this video
Jeff walks through building a mortgage calculator in a spreadsheet template, using his own example of a roughly $400,000 loan at 3.3% over 30 years, for a fixed-rate mortgage where the rate does not change. The monthly payment uses the PMT formula with the annual rate divided by 12, 360 periods (30 years times 12), and the loan amount as present value, adding a negative sign because PMT returns a negative number. He then builds an amortisation schedule: beginning balance, interest as beginning balance times the monthly rate, principal as monthly payment minus interest, any extra payment, and ending balance as beginning balance minus principal and extra payments, stressing extra payments go toward principal, not interest. Row two uses EDATE to advance the due date by one month and MIN for the monthly payment so the final payment is not more than the remaining balance plus interest, and the formulas are dragged down for the full term. Checking with MAX on cumulative principal should equal the $400,000 loan, and total interest in his example is about $230,000. He shows a 3% rate would cut total interest to $207,000 — $23,000 less — while 4.5% would add almost $100,000, and making a $5,000 extra payment each year for the first 10 years would save $53,000 in interest and finish by month 301, around 25 years. He notes the same template works for any fixed-rate loan.
Key takeaways
- Calculate the monthly payment with PMT using rate divided by 12 and periods as years times 12, and negate the result to show it as a positive payment
- In each schedule row: interest = beginning balance x monthly rate, principal = payment minus interest, ending balance = beginning balance minus principal and any extra payment
- Extra payments reduce principal directly, lowering the next month’s interest so more of each payment goes to principal
- Use MIN for the scheduled payment in later rows so the final payment does not exceed the remaining balance plus interest
- Small rate differences are large over 30 years in his example: 3% saved $23,000 versus 3.3%, while $5,000 yearly extra payments for 10 years saved $53,000 and ended the loan around month 301
Episode details: published September 20, 2022 · 7:47 long. Summary written by Self Growth Videos from the episode itself. Business results and any income figures mentioned are Jeff Su’s own claims, shared for education — they are not typical or guaranteed.
← Previous: 10 MUST-KNOW Macbook Tips for Productivity! · Next: Ultimate Guide to the Shortcuts App (for the Mac)! →
All Jeff Su videos · Jeff Su profile · Jeff Su (official site)
