![\"5](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/09\/financial-calculator4.png\" "\\"5")
<\/p>\n<\/p>\n
Microsoft Excel<\/a> can be used to record expenditures and incomes, create budget plans, forecasts, create data charts, and much more.<\/p>\n I have an Excel secret to share with you!<\/em><\/p>\n It can help you in making Excel Finance decisions<\/strong> by incorporating the worth of money in relation to time. It is an extremely useful tool<\/strong> for investment bankers and financial analysts.<\/p>\n When Using Excel as a Time Value of Money Calculator, you will be working on the following financial functions:<\/p>\n <\/p>\n Let’s look at each of these functions one by one!<\/p>\n <\/p>\n If you want to know the present value of an investment based on a series of future payments, assuming constant periodic payments and a fixed interest rate, you can use the Excel PV function<\/strong>.<\/p>\n In Excel Finance, future payments can either be periodic constant payments or a lump sum<\/strong> amount at the end of the investment period.<\/p>\n The syntax of the PV Function is:<\/strong><\/em><\/p>\n =PV(rate<\/span>, nper<\/span>, pmt<\/span>, [fv],[type]<\/span><\/span>)<\/p>\n What it means:<\/strong><\/em><\/p>\n =FV(interest rate<\/span>, number of periods<\/span>, periodic payment<\/span>, initial amount<\/span>)<\/p>\n To get a better understanding of this function in Excel Finance, let’s look at an example<\/strong>!<\/p>\n Follow the step-by-step tutorial<\/em> <\/strong>below to understand how to use time calculator Excel and make sure to download the workbook<\/strong> to follow along:<\/p>\n Download excel workbook<\/span>EXCEL-FINANCIAL-CALCULATOR-1.xlsx<\/span><\/a><\/strong><\/p>\n <\/p>\n<\/div>\n Example 1:<\/strong><\/span><\/p>\n You make an investment of $5,000<\/strong> each month for a period of 3 years<\/strong> at an interest rate of 6%<\/strong> per annum. Using Excel as a Time Value of<\/a> Money Calculator, calculate the present value of your investment.<\/p>\n \n<\/div>\n STEP 1:<\/strong><\/span> Insert the PV function in cell D12.<\/p>\n <\/p>\n STEP 2:<\/strong><\/span> Insert the first argument of the function – RATE (in cell D9)<\/strong><\/p>\n The periodic payments are paid monthly so the interest rate should also be monthly.\u00a0 Here, you will have to divide the rate by 12 to get the monthly interest rate.<\/p>\n <\/p>\n STEP 3:<\/strong><\/span> Insert the number of periods<\/strong> mentioned in cell D10<\/strong>.<\/p>\n Each payment is made monthly, so the number of periods will be the number of years multiplied by 12.<\/p>\n <\/p>\n STEP 4:<\/strong><\/span> Insert the constant payment amount<\/strong> mentioned in the cell D11<\/strong>.<\/p>\n <\/p>\n Once you insert the three arguments in the function, Excel will display the present value of the investment.<\/p>\n Make sure to keep the following few points in mind:<\/p>\n To follow the tutorial on the PV function by Microsoft Excel, Click Here<\/a><\/strong>.<\/p>\n <\/p>\n Excel’s FV function can be used to determine the future payment for a loan based on the periodic constant payment and a constant interest rate.<\/p>\n The syntax of the FV Function is<\/strong><\/em><\/p>\n =FV(rate<\/span>, nper<\/span>, pmt<\/span>, [pv],[type]<\/span><\/span>)<\/p>\n <\/p>\n Example 2:<\/span><\/strong><\/p>\n Suppose you have to borrow\u00a0$15,000<\/strong> at an interest rate of 3.5%<\/strong> (compounded quarterly) for a period of 5 years<\/strong>. What will be the loan amount you need to pay at the end of 5 years?<\/p>\n This can easily be calculated Using Excel as a Time Value of Money Calculator!<\/p>\n <\/p>\n Things to note here are:<\/p>\n So, the required formula<\/a> will be:<\/p>\n <\/p>\n Sometimes you know how much you want to invest now and how much you need but you are unsure of the time span. If you know the interest rate you can easily solve for the time period using NPER function.<\/p>\n The syntax of the NPER function is:<\/strong><\/em><\/p>\n =NPER(rate<\/span>, pmt<\/span>, pv<\/span>, [fv],[type]<\/span><\/span>)<\/p>\n <\/p>\n Example 3:<\/span><\/strong><\/p>\n You have $900<\/strong> to invest today. In how many years will it double<\/strong> if you invest it at an interest rate of 11% compounded annually<\/strong>?<\/p>\n For this example, the present value will be $900 and the future value will be $1800. Since there is no periodic payment, PMT will be 0.<\/p>\n Lastly, you have to change the sign of either PV or FV to negative<\/strong>. It has to done to indicate Excel that one of the amounts is an inflow and the other one is an outflow.<\/p>\n If you skip doing this and leave both PV and FV as positive values, Excel will provide a #NUM error instead of giving you an answer.<\/p>\n The required formula for Using Excel as a Time<\/a> Value of Money Calculator will be:<\/p>\n So, in approximately 7 years<\/strong> your $900 will turn into $1800 if invested at an interest rate of 11% per annum.<\/p>\n <\/p>\n The RATE function in Excel<\/strong> can be used to find the interest rate for discounting the future value<\/strong> of the investment in present value calculation.<\/p>\n The syntax of the RATE function is :<\/strong><\/em><\/p>\n =RATE (nper<\/span>, pmt<\/span>, pv<\/span>, [fv],[type],<\/span>[guess]<\/span><\/span>)<\/p>\n The last argument of this function is “guess”. It is an optimal argument that is used to provide Excel with an estimate of what the rate could be. If omitted, the default value will be 10%.<\/em><\/p>\n <\/p>\n Example 4:<\/span><\/strong><\/p>\n Let’s find out the interest rate on a home loan of $35000<\/strong> that has to be paid over a period of 30 years<\/strong> with a monthly installment of $5000<\/strong>.<\/p>\n In this example, you can insert the monthly payments made ($5000)<\/strong>, the present value of the loan ($35000)<\/strong>, and the number of periods (10 years)<\/strong> in the RATE function<\/strong> to get the interest rate.<\/p>\n Please note that since the monthly payments are an outflow and the present value of the value received is an inflow, you have to add a negative sign<\/strong> in front of PMT value to get the answer.<\/p>\n The required formula will be:<\/p>\n This is how you can calculate the loan rate<\/a> by Using Excel as a Time Value of Money Calculator.<\/p>\n <\/p>\n The PMT function calculates the periodic payment<\/strong> against an investment or a loan at a constant interest rate<\/strong> for a specified period of time<\/strong>.<\/p>\n The syntax of the PMT function is :<\/strong><\/em><\/p>\n =PMT (rate<\/span>, nper<\/span>, pv<\/span>, [fv],You can also do Financial Calculations\u00a0using Excel as a Time Value of Money Calculator<\/span>.<\/strong><\/h3>\n
\n
<\/a>1. Present Value (PV)<\/strong><\/h3>\n
\n
\n![\"5](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/08\/46b1e58ce4921843f35857686cd3b3c9.png\" "\\"5")
<\/a><\/p>\n\n=PV(<\/span><\/h3>\n
![\"5](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/08\/Screenshot-616.png\" "\\"5")
<\/a><\/p>\n=PV(D9\/12,<\/span><\/h3>\n
![\"5](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/08\/Screenshot-617.png\" "\\"5")
<\/a><\/p>\n=PV(D9\/12, D10*12,\u00a0<\/span><\/h3>\n
![\"5](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/08\/Screenshot-618.png\" "\\"5")
<\/a><\/p>\n=PV(D9\/12, D10*12, D11<\/span>)<\/h3>\n
![\"5](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/08\/Screenshot-619.png\" "\\"5")
<\/a><\/p>\n![\"5](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/08\/Screenshot-621.png\" "\\"5")
<\/a><\/p>\n\n
<\/a>2. Future Value<\/strong><\/h3>\n
![\"5](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/08\/Screenshot-622.png\" "\\"5")
<\/a><\/p>\n\n
=FV(D9\/4, D10*4,,D11)<\/h3>\n
![\"5](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/09\/Screenshot-330.png\" "\\"5")
<\/a><\/p>\n<\/a>3. Number of Periods (NPER)<\/strong><\/h3>\n
![\"5](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/08\/Screenshot-624-e1598698389581.png\" "\\"5")
<\/a><\/p>\n=NPER(D9, 0, D10, -D11)<\/h3>\n
![\"5](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/08\/Screenshot-625.png\" "\\"5")
<\/a><\/p>\n<\/a>4. Interest Rate (RATE)<\/strong><\/h3>\n
![\"5](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/08\/Screenshot-627.png\" "\\"5")
<\/a><\/p>\n=RATE (D9, -D11, D10)<\/h3>\n
![\"5](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/08\/Screenshot-628.png\" "\\"5")
<\/a><\/p>\n<\/a>5. PERIOD PAYMENTS (PMT)<\/strong><\/h3>\n