MVP #88: The Excel Formulas That Help You With Your Personal Finance and Investment Plans

In september, we had a webinar on Using Microsoft Excel for Investment Planning, Personal Finance & Financial Analysis. Many people loved it and the practical application in their own personal finance and family finances/investment planning. You can still watch the recorded video on YouTube.

Today, however, I have decided to do the written version of it so many more people can benefit from it. You can download the practice along Excel file here.

There are five major formulas you need to have a good grasp of if you want to put some structure and calculations around your financial goals and investment strategy. And they are:

1. PMT
2. PV
3. FV
4. RATE, and
5. NPER

PMT

PMT is the Excel formula you use to determine how much you need to save regularly (weekly, monthly, yearly) in an interest bearing account to achieve a particular set financial goal (education fund for your children, buy a house, buy a car or be a multi-millionaire). It is also the formula you use to calculate how much you pay back monthly or yearly for a loan that requires you to pay back the same amount (to cover both interest and principal) over the lifetime of the loan.

How it works is very easy. And below are the transcripts of the demonstration in the video and practice file.

a. Want to have N20 million saved in an education fund for your children by 2030 (13 years time).

Question: How much should you contribute in a 15% annual interest bearing savings/investment account?

PMT has five parameters:

• Rate: The interest rate. And in this sample case, it is the 15% annual interest rate.
• Nper: The number of periods. In this case, it is the number of years you plan to save for, 13 years.
• Pv: Present Value. How much you currently have saved in the investment account. In this case, since we are starting from scratch, that would be zero.
• Fv: Future Value. The final amount you want in the investment account by the end of the specified period. In this case, 20 million Naira. Notice the minus I put in the formula, it's just a technical way of getting Excel to show the answer in a positive value. That's all.
• Type: Will you be saving at the end of the year or at the start of the year. For end of year, you put 0 or leave it empty, and for at the start of the year, you put 1. The logic applies for other types of period -- weekly, monthly etc.

One very important thing to note is that the rate and number of periods must agree. You can't use annual rate to calculate monthly contributions without first converting that annual rate to monthly rate. You should check out the part in the practice file on monthly contribution for the same goal to get a good grasp of how to convert from annual rate and annual period to monthly rate and monthly period.

b. You bought a 3 bedroom flat for N100 million and were allowed to do 25% down payment while flexible spreading the remainder in a monthly repayment at a 2% monthly rate.

Question: How much will you pay if you spread the payment over 5 years (60 months)?

Try figure this one out and compare your answer with mine. 

In this sample case, PV is the loan amount outstanding. NPER is the duration. Rate is the repayment interest rate. FV is zero since you mustn't owe them anymore at the end of the duration nor pay them excess. And TYPE is at month end repayment (hence, the leaving empty).

PV

PV is for calculating the worth of an investment project after factoring the cost of the capital (loan interest rate, if you borrowed the money from the bank). 

c. You are part of board of directors for a manufacturing company. They are considering a business idea that will cost N300 million and generate N50 million every year into the 9th year.

Question: If the company's cost of capital (discount rate) is 15% what will be the present value of the business idea if executed and is it a profitable one?

Notice how there is all those components of PMT formula in there -- Rate, Nper, Fv and Type. They still mean the same as previously explained. This time, though, we have the periodic inflow/payment from the business project (same as PMT) and want to calculate the present value/worth (PV). If we can be able to service the cost of the capital deployed and still turn a profit (pay back both the interest and capital of the borrowed money to finance the business idea, and still have something left as profit/compensation for all our stress).

Again the minus in the PMT box is to get Excel to display the final answer in a positive figure. Nothing more.

FV

FV is a very interesting and useful financial planning formula. It allows you estimate how much you will have in an interest bearing account if you do consistent periodic (weekly, monthly or yearly) contribution/saving. 

d. You invest/save N40,000 monthly in a 15% interest rate investment account. 

       Question: In 10 years, how much will you have? What about in 30 years?

 

I don't think I need to explain how this work, based on all my previously explanations you should be able to figure out how it works.

Also notice how the future value stratospherically increase when you save for 30 years as against the 10 years? That is the power of compound interest.

RATE

This is useful for those who take microfinance loans or co-operative loans or buy land on installment payment or any loan that you are required to pay specific constant amounts regularly. It would make a lot of sense to calculate what the actual rate of the loan is, since most times they don't provide you the rate.

e. My dad took a 6 months loan of N400,000 from LAPO Microfinance bank. He would be paying back N479,000 split into equal monthly payments.

Question: What is the rate he was given the loan at?

No comment. You go figure out.

NPER

This is useful for calculating how long it would take you to finish paying for a car or home if you opt for an installmental payment amount you choose. 

f. Your company has a car loan scheme. You took N4 million loan to buy a car and you are allowed to make monthly repayment at a 2% monthly rate.

Question: How long will it take you to pay off the car loan if you make a monthly payment of N200,000?

No comment. You go figure out.

Don't forget to watch the demonstration video and work along with the practice file.