Financial Functions of MS-Excel

 MS-Excel Financial Functions

We at I For Investment Financial Consultancy put our best for the investors education and clients ease in understanding investment easily. We are going to begin with the Financial Functions by Microsoft Excel, there are some complex functions in Microsoft Excel we will try to cover the important ones. 

let us begin with the Financial Formulas of Microsoft Excel, Today we are going to explore our First Formula of FV (Future value of our onetime investment or series of Investments) To perform this formula we should know these concepts as mentioned beloww

1] PV:- Means Present Value

2] Rate:- Means Rate of Interest on the investment

3] NPER;- Number of Years of being invested means a duration of Investment

4] PMT:- Series of a regular payment, this may be monthly, quarterly, semi annually or yearly

Let us assume that Mr. A wants to invest Rs. 100 in a Fixed Deposit where he will be paying only once and Bank has promised him to pay 8% p.a. as an interest. He wants to keep this investment for 3 years and want to know how much will be the value of his investment of Rs. 100.

To understand this let us use Microsoft Excel or Google Spreadsheet where we can calculate it for the answer. To explore this function we can click on "Formulas" Tab of Microsoft Excel, then We have to click on Financial , then FV, then a box for formula's value will appear there which will contain spaces to put Rate, Nper, Pmt, pv, type, in the first space we will type 8% as this is the interest rate committed by bank, here we will have to ensure that we don't forget to type a symbol of %. After that in the space of Nper we will type 3 as we will keep this investment for 3 years, we will not type anything in pmt as we are not going to make a regular series of payment and our investment is only onetime investment of Rs. 100, then in the space of pv, we will type -100, we will use a symbol of "-" because this payment is going from our pocket, after than in the space of Type we will type 1 and it will reflect the answer as Rs. 125.97 as the returns on my onetime investment of Rs. 100. The holding period of this investment is 3 years here in this example.

If we have to perform this formula manually in excel then We have to type =FV(B2, B3,,-B1,1)

Please refer to the image attached with, here B2 indicates Rate of Interest, B3 indicates the duration, we have added comma twice because here we are referring onetime investment and not the series of regular payment, if this payment would have been regular then we had to type it in pmt, however this was the example of onetime payment and we had not put anything there with reference to payment, then we had to add a symbol of - with B1 because this payment goes from the pocket of investor, if we do not add the symbol of - then the answer will show in negative returns, therefore we have to ensure that we add minus symbol with the onetime investment or regular pmt, then we added 1 because we are getting the interest calculation at the beginning of the period. please refer to the attached screenshots to perform this formula and function in MS-Excel or Google Sheets.

Now, let us understand the same function considering that the payment is not onetime investment but in a regular installments such as yearly, half-yearly, quarterly, monthly. here we will use the same function with certain changes, let us see it with the example, Mr. A has decided that he will invest Rs. 100 every year for 5 consecutive years, let us understand what will be the value of his investment if he gets 10% annual returns on his investment. To perform this function in Microsoft Excel, we will click on "Formulas" tab, then "Financial", and then "FV". It will prompt another window where we can put the formula details or the cell details, In this example the payment is yearly, therefore we will add it in the space of PMT and the payment is going from the pocket of customer, hence we will add a symbol of minus so that the answer comes with a positive value. In the previous example we put the investment value in the space of PV, and in this example the payment is yearly and not onetime, thus we will add that investment value in PMT as these are series of payment . You may refer to the screenshots given below which can help us to perform this function.

Now let us consider that his payment is monthly then in the space of PMT we will add either -1200 or -100*12, here we are multiplying the PMT into 12 because there is a monthly payment it means in a year there will be 12 installments of Rs. 100, refer the below mentioned screenshots for the details. if this payment would have been quarterly then in the space of PMT we would have typed either -400 or -100*4, similarly if this payment of Rs.100 would have been half yearly then we would have mentioned it as either -200 or -100*2. Here we have to type PMT figure as per the mode of payment and ensure that we don't forget to add symbol of minus (-) before the PMT amount.

Let us make this example as more interesting, Let's assume that Mr. A wants pay these installments for 5 years with yearly mode of payment and wants to withdraw his amount after 7 years. Let us calculate the maturity amount with the same example. Here we will have to perform 2 calculations as the payment is there for limited period which is popularly known as Limited Premium Payment (LPP), First we will calculate the value of investment of Rs. 100 per year for the investment duration of 5 years then we will get the future value of that investment, in the another formula that FV will be a present value and we will calculate its future value after 2 years, let us refer to screenshots given below.

In this example Mr. A will invest Rs. 100 per year for next five years and he will get Rs. 813 after 7 years, This concept of investment is called as investment by Limited Premium Payment, generally it gets used in Unit Linked Insurance Products (ULIPs) of Insurance companies.

You may change the figures and check the various answers, Please do not hesitate to call us if you come across any query. Wish you a happy learning from I For Investment Financial Consultancy, stay tuned for another exciting formulas soon. Cheers to the beautiful life.

11

1 share

Like

Comment

Share