Financial Functions and Formulas in Excel
Let Excel help you make Money and/or keep it.... (;-)
Excel has very powerful tools, functions to solve financial problems. There are more than 50 financial functions to choose from that allow you to make very complex formulas
Some are so specific to certain fields or the industry (like share market, securities, treasury) that I will list them here but not go, yet, into the details. I will explain here the most commonly used ones.
Let's come back to our Apple Orchard (or your own small enterprise). What are the common daily issues we are confronted with.
- Repayment of Loan or Mortgage
- Interest paid on loan at any given time
- Net present value, return on investment (in order to make strategic decisions)
- All the other financial functions and formulas briefly described.
Repayment of a Loan or Mortgage
Our Apple Farm has made a loan in order to buy some equipment, tools and furniture (our baskets, etc...)
The Loan amount was £75'000 and it has to be paid back over the next 2 years. We negociated an interest rate of 3%.
Excel provides us with a function called PMT which returns the periodic payment for this kind of loan.
PMT(rate, nper, pv, [fv], [type]) where
rate is the interest rate over one period (so if you pay every month, the rate will be the annual rate / 12)
nper is the total number of repayment (so if monthly loan repayment over 4 years, these are 4 x 12 = 48 repayments)
Pv is the loan amount (here present value or also known as the principal)
Fv is the future value (optional, you can leave it and Excel will assume you repay the full loan but you could decide that at the end you want a residual value to be paid
Type is optional and lets you decide if the payment is at the beginning of the period (beginning of the month, type= 0) or end of the period ( type = 1)
The formula out of PMT is here under.
So you can see, our loan is quite heavy because we decided to reimburse it in 2 years, 24 repayments of $3290.35...... .We will have to sell a lot of apples....or sell them very expensive.
REMARK: The PMT returned value is NEGATIVE because you are repaying something so beware in your calculation
You could also name the various cells in order to make this more readable. Look here how this is done.
Interest paid on loan at any given time
Now lets say you want to know how much interest you will be paying after the 10th installement (after 10 month). The function IPMT will do the trick.
IPMT(interest/12,payment_number,duration*12,original_loan,residual_value)
Be careful not to mix up the function ISPMT and IPMT
ISPMT assumes that the payments on the principal are constant over time, while the total payments (interest + payment on principal) vary over time.
IPMT assumes that the total payments are constant over time, causing both payments on the principal and interest payments to vary, as in an annuity.
In the case of our Apple Orchard, here what it looks like. You can see that the total monthly payment (column H) are always $3290.35, but as time passes, the amount due (column D) goes down and so does the interest (column E). Therefore, our payments are going faster and faster towards reimbursing the loan instead of paying the interest.
Life lesson (:-) : Avoid being too much in debt because all you are going to do is reimburse the interest instead of the loan.
or (this is a bad lesson!!!) be so much in debt, I speak about reaaaallllllly in debt, like Europe, USA or Japan and then the banks will loan you more and more and have no other choice to do this.....
You could also name the various cells in order to make this more readable. Look here how this is done.
This file can be found here.
Net Present Value (NPV function)
The NPV calculation allows you to make investment decision based on what you expect your investment to bring to your company.
For exemple in the case of our Apple Farm, we invested in a new packing line costing $20'000.
The estimated cash flow from this investment is estimated at 1000 for the first year, then 2000, 4000 and 6000 for the following years.
This cash flow comes from improved productivity, saving, etc....
I can see that after 7 years (which is the minimum life of my machine) the Net present value will be 2370, therefore my investment is worth it. I took 7% as an interest rate which is much higher than bank interest but it iincludes the risk too. So in reality, it is much before 7 years that my machine will be worth the investment.
This file can be found here.
All Other functions
This list is the full list of financial functions that Excel provides you with.
So that's it for the Financial Formulas and Functions in Excel.
Now, let's use them