Excel Magic Trick 1007: Loan PMT Semi-Monthly Schedule: A Few Finance and Date Tricks

Learn these things:
1. How to use PMT function
2. Period rate calculation
3. Cash flow direction: negative or positive
4. Fill Handle Smart Tag to fill dates by month: this allows to to populate a column with only dates at the 15th of each month, or the end of the month.
5. EDATE function to increment dates for the 15th of each month with a formula.
6. EOMONTH function to increment dates for the end of each month.
7. Absolute and relative cell references.
8. Build a semi-monthly amortization table.

[excelisfun]

Yes, as an economist, you know all the bankers trick! Amort tables help us to see clearly!!

[excelisfun]

I don't have a video on this, but if you build the two Amort tables and then subtract the interest, or use 2 IPMT functions and subtract the two, you could determine the difference.

[TickyMN]

You're right Mike. Thank you for bringing this to my attention. My logic told me I would save interest. Thank you for the follow up video. I'm going to practice that.

[excelisfun]

BTW, it always depends on the contract - the vast majority of contracts do not allow for benefits to the borrower if you pay early...

[TickyMN]

Great video as always Mike. I pay my mortgage the day I get paid close to the 15th of each month. Could you demonstrate how to calculate the interest savings by doing this?
Thanks for everything Mike!

[ExcelStrategy]

Ammortization table it's superusefull to avoid the tricks of the banksters ! lol and great use of EDATE and EOMONTH !

[Coouge]

When paying bi-weekly payments, is the interest rate calculated from the remaining balance at the *end of month* ?

[excelisfun]

In the last post I suggested the incorrect function. It is not IPMT, it would be: CUMIPMT.

[triciadonovan]

Can you do a video showing an auto amortization?

[meelanzxcvbnm]

It would be great if you could post a video, an amortization table in excel with grace period, where you could structure interest only for say the first 3 years and then principal for the remaining term of the loan

[allorabuctolan]

Trying following thru but sadly cannot type when holding control key.

[johnnyLikeVideo]

If you get a biweekly payment, do you put 26 instead of 24?

[bhavneshparikh]

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