Many people see Excel as a tool that is good for business applications only, but when you look at the simple and useful formulas given below, you will see that Excel Formulas helps you solve your financial problems. Could.
So let’s see how you can make your financial calculation in Microsoft Excel.
1) Compare Any Loan Terms:
Are you going to take a new bike, car or house for yourself? You can get confused with various types of financial schemes and bank’s interest rate and loan term.
Whenever you want to know about the actual monthly payments by comparing any loan term and interest rate, take advantage of the powerful (and simpler) PMT formula.
The PMT function calculates the payment of that loan with constant payment and constant interest rate.
= PMT (rate, nper, pv, [fv], [type])
rate – Interest rate for loans.
nper – Total number of payments for the loan.
pv – the current value, or the total value of all loan payments.
fv – [Optional] After the final payment, the future value, or the cash balance you want. 0 (zero) for default
type – [Optional] When payments are due. 0 = end of a period. 1 = Beginning of Period. The default is 0.
Example: Calculate Payment on Personal Loan
Using the PMT function, you can find out how much you will need to specify for specific interest rate and loan term.
For example, if you are taking 10,000 loans for 24 months with an annual interest rate of 8 months, then PMT can tell you what your monthly payment is.
= PMT (rate, periods, -amount)
In the example shown, D3 has a formula:
= PMT (C3 / 12, B3, -A3)
As you can see, when you can compare many loan term simultaneously, there are some realities coming out.
2) FV – Future Value:
Do you want to invest your money in Fix of Deposit (FD)? So this next formula can make your work easier.
With the help of this formula, you can compare the interest rate of different banks and see how much money you can get back after the period.
Excel’s FV function is a financial function that shows the future value of the investment. You can use the FV function to achieve the future value of the investment, with Constant Interest Rate, conforming Constant Payment.
Getting the value of the investment value.
= FV (rate, nper, pmt, [pv], [type])
rate – loan rate
nper – Number of payments (or investment period in months)
pmt – Payment made in each period. (Usually monthly)
(This number should be negative.)
pv – [Optional] If the current initial balance (optional) is not, then it is considered to be zero. Should be entered as a negative number.
type – [Optional] When payments are due. 0 = the end of the period, 1 = the beginning of the period. The default is 0.
For example, let’s assume that you pay 1000 per month for 10 years at an annual interest rate of 5%.
In the example shown, E3 has a formula:
= FV (A3 / 12, B3, -C3, D3)
3) Calculate Required interest rate to grow:
If you have cash, which you want to make a specific increase in the future, then you can see what the required interest should be in Excel.
Excel’s RRI function lets you calculate the interest rate.
Note: The RRI function is only available in Excel 2013 and beyond.
Let’s say that you have 10,000 rupees in that date, which you want to increase to 25,000 after 5 years. So you will need such a rate of interest for it?
RRI (nper, pv, fv)
nper – Investment Period
pv – The present value of the investment.
fv – The value of future of investment
The following spreadsheet Excel RRI function is used to calculate the interest rate required to invest 10,000, so that its value can reach up to 25000 in the 5 year period.
The formula in cell B5 will be like this.
= RRI (B2, B3, B4)
The result of this formula is the quarterly rate. When it is multiplied by 4 to translate at an annual rate, then the answer is 0.2011×4 = .08 or about 8%.