Let us learn 5 Excel functions that are useful for personal finance
Effective Interest Rate (Effective rate)
Example: In the case of bank deposits, Bank gives an annual interest of 7%. Here is the variation. One bank gives interest once a year. Another bank gives interest every six months but at the same 7% annual interest rate. If another bank gives interest once every three months, then we have to choose the bank which gives interest for a short period of time. This will give higher yield, which is slightly better than 7% per annum. Let us find how yield differs from bank to bank in the above example using excel effect function.
The function Effect is used to compute the yield.
Used factors / variable:
Information to input: (You can understand how to enter the Excel by seeing the picture below)
The yield for 7 per cent interest per annum is 7.19% when we receive interest once in three months.
Refer the table below to understand effect of getting monthly interest. It pushes the yield up by 7.23% per annum, even when the interest rate is only 7% per annum.
Interest Frequency
|
Yield
|
Once in
quarter
|
7.19
|
Once in
half year
|
7.12
|
Once in month
|
7.23
|
In another example in Nov 19, popular NBFC is giving 7.72% interest – Interest distribution is every month, using the effect of 12 npery and rate of 7.72%, using effect function in excel we get 8% per annum interest.
Refer the image below for the same
Click the link to access the working model of the Payment template in google sheets - You are free to change the variable and get the desired results. You can download and share with your contacts who are interested in this functions / learning excel
Click here to read the NEXT article about using XIRR function: Mutual fund returns can be calculated using XIRR. The SBI Magnum multi-cab scheme is taken as an example and the period of investment is four years
Click here to read the PREVIOUS article about using FV function: Radha thinks he can buy a house in 10 years. Current price is 40 lakhs. Expected Inflation is 7%. Then how much money should he save now to buy a house in ten years?
No comments:
Post a Comment