It is the 4th day of Your week @ PHD and we have already posted some excellent array formulas, productivity hacks and other very useful tips.
Must read: part 1 of excel tips shared by readers | part 2 | part 3
Sticky Notes and Memorizing formula Syntax by Teyln
I have a Post-it note stuck near my monitor to help me remember the syntax of the INDEX-MATCH combo function. It says:
=INDEX(range,^,column#)
MATCH(what,where,FALSE)
The caret ^ in the Index function is where the Match function goes.
Find How Much Experience an Employee Has by Rajinikanth
Formula to find the Employee Experience, if the date Format is text like(Wednesday, March 10, 2004) :
Suppose employee joining date is Wednesday, March 10, 2004
Todays date is : =today()
then the formula to find the Employees Experience in the company like(No.of years, Months, Days) :
=((TEXT(DATEDIF(D11,E11,”y”),”0″)&” ” &”Years”)&”,”&(TEXT(DATEDIF(D11,E11,”ym”),”0″)&” ” &”Months”)&”,”&(TEXT(DATEDIF(D11,E11,”md”),”0″)&” ” &”Days”))
PHD’s note: The formula =TEXT((NOW()-E11)&"",”yy ""years"" m ""months"" dd ""days""") also works and is probably much simpler. It is mentioned in the 100 excel tips post.
Macro reduces the blood pressure by Michael
Perhaps it’s an artifact of my profession, or perhaps it’s a bias I formed back in the Lotus days.but I have never been really comfortable with Microsoft’s apparent distrust of recursion. True, non-convergent recursion can induce a migraine, but those “circular reference” alerts are almost as annoying.
So, for quite a while, I’ve had the following bit of VBA included in my Book.xlt/Book.xltm file:
Private Sub Workbook_Open()
Application.Iteration = True
End Sub
Voila! A pet peeve and annoyance resolved.
Thank you
Thanks to everyone for actively participating in the your week @ PHD. Donuts to Vishy, Rajinikanth, Barbara for sharing several tips. It has been very exciting and fun to learn new tricks from you. I hope you had as much fun learning.
Tomorrow we will have a open thread for all of you to chip in with more tips.
Also, Let me know if you want the your week to be a regular feature.
20 Responses to “Mortgage Calculator with Extra Payments – Excel Download”
Kia ora Chandoo, thanks for this wonderful template brother. How can I modify this/ or do you have a similar template for a fortnightly
payment? Thank you
Morena Rayd. You can adjust the multiplier / divider to 26 (I think it was set to 12) to see fortnightly details. Watch the companion video to understand the steps / logic.
It is very simple and easy to use. Is user interface is attractive. It is very helpful and beneficial for calculations.
This is great, thanks a lot!
I think there is an error in the "You save X $ in interest". If i dont put any extra payment, it still says that I will save 19k in interest rate.
I too realized this problem. I will post an update once I fix the error.
Updated on 11-Sep: I fixed the problem with "interest saved" calculation. Turns out there was an internal bug in cumipmt function in Excel that reports incorrect values. Something new!
Please use the link above to re-download the correct file.
Hi Chandoo, do you have a template that will make changes to figures going forward if the interest rate changes part way through the loan? Thank you for your help.
I wanted calculator which can consider floating interest rate, can you please advise how to get that?
I love your tutorial. If I am making weekly acelerated mortgage payments, how to I account for that in the Eff term?
Thank yo so much,
Christine
Hi Chandoo. I want an excel template where I can have my favourite stocks listed for which the prices changes on a dynamic basis every day as per the market. I mean a sheet which is linked to NSE for price changes and other changes like daily low, Daily high, Yesterday closing, last 10 day range, 52 week high, 52 week low, one month high, one month low, one week high and one week low etc
The excel is awesome but I am getting #NAME? error with the field 'Old Interest Paid' and subsequently Original Interest and Money saved. Can you tell me how to fix that. I am trying to check if I should complete my loan by the extra payments or instead invest the same amount elsewhere
#NAME error could mean your Excel doesn't have one of the functions I am using. Can you tell me what version you are using? If I am not mistaken, this file requires Excel 365.
Hi Chandoo, I am also getting the same error. I am using Office 2019. Would you be able to recommend a fix for this?
Regards,
How do you change the dollar to rupees for whole sheet to match indian currency
You can select the values and apply rupee formatting from cell formatting options.
Hey Chandoo,
Thank you for making the excel spreadsheet! It is great, but there another, little known money saving action that you can take to DRAMATICALLY reduce your total interest owed on your mortgage allowing you to pay it off 40% to 60% faster. I am going to share with you for free. It's called recasting your mortgage, which is a form of re-amortizing the interest that you owe on your loan by paying the the bank an early payment of $10,000 or more. The bank will modify your loan to cancel the original interest that you owe, and recalculate the total interest based on the new lower loan loan amount for the remaining term of the loan, instead of the original loan amount. This pays off the most expensive section of interest on your loan which is the next owed interest, instead of cancelling the interest at the end of your loan. Your charts show the interest being canceled on the last payments, however the interest can be canceled on your next upcoming portion of the loan, but keeps the ending date the same. However, the result of recasting the loan is you, pay the loan off 2 or 3 times faster, by paying off the portion of the loan with the highest interest cost. I am wondering if you can build a excel spreadsheet that shows this for me? I will pay you for making it. I am a mortgage loan officer and would like to use it to help my clients see the impact of paying off their loans this way. Thank you for considering this!
HI Chandoo.
Excellent sheet i watched your video and tried to edit sheet to suit my current mortgage however i cant get it to work. I am trying to set it to weekly payments.
I edited the monthly payments to =PMT(E7/52,E6*52,E5)
eff.term =ROUND(NPER($E$7/52,$E$10,$D13),0)
principal term to =PPMT($E$7/52,1,E13,D13)
cb w/o to =IF(C13=1,$E$5,J12)+PPMT($E$7/52,C13,$E$6*52,$E$5)
and interest paid to =IPMT($E$7/52,1,E13,D13) but i cant edit the arrays to increase the range to 1560 from 360, so i just added the numbers in and that worked for the array on the left but the old interest paid array im stuck. And the graph well the graph is useless now and i have no idea how to fix that. no idea at all.
Any help would be great.
Cheers
To change the NUMBER OF ROWS, go to cell C13 and change the formula to
=SEQUENCE(E6*52)
The chart won't auto-adjust. So you need to adjust the chart's source data range to capture all rows.
Hey man, I really appreciate your contribution, this template is awesome, I am sure it will help me to plane my financial life, and also to pay debts earlier and create wealth.
Thank you!!!
how do i edit this to use months instead of years for term? i have a 90 month loan (7.5 years) that i am trying to use with this and it forces me to use 7 or 8 in years.