This post is written by Paramdeep.
Today, let us learn how to use NPV() function in Excel & create a simple financial model.
NPV – Introduction:
If you are dealing with cash and valuations, you are bound to have come across the NPV function. If you don’t know the assumptions behind the same, I bet it could cost you your job!
Let’s take a simple project – You buy a MSFT stock for USD 100. You receive a dividend of USD 10 in the first year, USD 20 in the second year, USD 40 in the third year and then you sell it out for USD 140. If you could have alternatively put this money in bank at 10% interest rate, have you gained anything?
How do you model this in excel? In this tutorial we understand how you can use NPV to do this analysis and what kind of pitfalls you can land into!!
What is the NPV() function
Simply speaking, NPV function calculates present value of your cash flows. Let’s take a simple example first –
You invest $100 in a bank, which pays 10% interest
- What is its value 1 year down the line?
- I am sure, you don’t need any coffee to get that value – 100 x 10% is the interest and 100 is the principal that you had. So the value 100 x (1+10%) = 110
- What about 2 years?
- Simple 100 x (1 + 10%)^2 = 121
- So, if I were to ask you, the present value of a cash flow of 121, that you were to get 2 years down the line at 10% interest?
- Again simple, you told me initially, it was $100
NPV does exactly that – gets you the present value of your cash flows
The function is simple, it does all the difficult calculations for you and gets you the solution!
Beware – The function has its own assumptions!
Though the function is quite convenient, but it has its own pitfalls. And in my modelling experience I have seen a lot of people making that mistake! Lets model the situation described in the beginning (The MSFT Case). The cash flows are given to you as:
Let us see, internally what we get by modelling the NPV from the first principles and using the NPV function
You can clearly see that there are two ways of using NPV function (and each has its own assumption!)
So what is happening internally?
Usually when we start a project, we assume that the investment is made upfront (On day 0). Then the revenues, costs and the cash would start flowing in. Since the investment is made on day 0, it should not be discounted.
But when you use the NPV function, excel internally makes an assumption that even the first cash flow is at the end of the year (Per se, this is not wrong, but in normal circumstances, you make the payment upfront!).
So the right usage of the function would be to add the first cash without discounting and then use the NPV function to discount the rest of the cash flows.
If you just use the NPV function on all the cash flows, then the inherent assumption is that even the first cash flow is at the end of the year.
Few other ways of calculating NPV
When you are dealing with cash flows and valuations (typically that is when you come across the functions like NPV, etc) even small mistakes cost dear. You want to make sure that you are as accurate as you can ever be. At that point of time, if the cash is not flowing at the year ends, you can use a more powerful function in excel – XNPV. You can show it the cash and the exact dates and it would calculate the exact NPV for you. People don’t often use it as they don’t know the exact dates of cash flow!
How do you calculate the discounted cash values in your models?
I know the easiest way would be to use the NPV function. It is easy to use but at the same time could be tricky. So how do you implement such functionality in your models?
Templates to download
I have created a template for you, where the subheadings are given and you have use the functions to get the right values for you! You can download the same from here. You can go through the case and fill in the yellow boxes. I also recommend that you try to create this structure on your own (so that you get a hang of what information is to be recorded).
Also you can download this filled template and check, if the information you recorded, matches mine or not!
For any queries regarding the cash impact or financial modeling, feel free to put the comments in the blog or write an email to paramdeep@edupristine.com
Join our Financial Modeling Classes
We are glad to inform that our new financial modeling & project finance modeling online class is ready for your consideration.
Please click here to learn more about the program & sign-up.
Learn more about Financial Modeling:
Go thru these articles to learn more about Excel Financial Modeling:
- Excel Financial Modeling – 6 part tutorial
- Introduction to Project Finance
- Using MOD() function to implement frequency escalation in Excel
- Creating a P&L Reporting Model in Excel – 6 part tutorial
The article is written by Paramdeep from Pristine.
Chandoo.org has partnered with Pristine to launch a Financial Modeling Course. For details click here.
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.