In a recent consulting assignment I had a tricky data validation problem. The customer wanted to have an either-or condition in the data validation, like this:
My initial reaction to this requirement was “hmm… that is not possible“. But before shooting the email back to client, I got curious and checked if excel data validation can actually do this. And of course we can do this in Excel with ease.
First see the demo of how this would work:
Now to the specifics:
- Select both cells where you want this data validation to be applied.
- Now go to data validation (Data Ribbon > Data Validation or Data Menu > Validation)
- Specify validation type as “Custom” and use a formula like COUNTA() to check count of cells with a value (see the illustration)
- Optional: Use Error Message settings to set a message you prefer.
- That is all. Now your Either Or Data Validation set up is done.
Download the example file:
Click here to download the example file with this kind of data validation setup. Play with it and learn how to do this on your own.
Learn more about Data Validation in Excel:
Read more about adding a drop down list validation or advanced data validation tricks or all of them.
Related: Writing XOR (either or) formulas in Excel
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.