10 most used financial functions in Excel
TOPICS COVERED
Email: Support@skillfinlearning.com
Mobile: 9650010925
NET PRESENT VALUE (NPV) – (1/3)
• Net present value is the present value of cash inflows minus present value of cash outflows. • It is used for making capital budgeting/ investment decisions. • Excel has an inbuilt NPV calculation formula, which calculates the net present value of a series of cash flows at a given discount rate. • Negative Cash flows are considered as cash outflows while positive cash flows are considered as cash inflows. Email: Support@skillfinlearning.com
Mobile: 9650010925
NET PRESENT VALUE (NPV) - (2/3) NPV SYNTAX = NPV(rate, Value1, [value2]‌.) Rate is the discount rate to be used for discounting the cash flows. Value1 is the cash flow at the end of Period 1 Value2 is cash flow at the end of Period 2 User has the option to discount up to 254 cash flows using NPV function. The limitation with NPV function is that excel assumes all the cash flows to occur at the end of the period. Therefore, in for an investment done at the start of the period, add it separately for NPV calculation. Email: Support@skillfinlearning.com
Mobile: 9650010925
NET PRESENT VALUE (NPV) – (3/3) EXAMPLE John is considering the financial viability of an investment. The cash flows are as follows.
To calculate NPV Type = NPV(rate, cash flows) = NPV (D11,D5:D9)+D4
We get the NPV of $ 25,185.19 for the investment. That means the investment is acceptable as the present value of cash inflows is greater than present value of cash outflows. Email: Support@skillfinlearning.com
Mobile: 9650010925
XNPV –(1/3)
• XNPV is Extended NPV.
• It measures the NPV of a stream of scheduled cash flows. It is an advance version of NPV. • The difference between NPV and XNPV in Excel is that in NPV cash flows are considered to occur at the end of each period, while in XNPV the cash flows occur at dates defined in the model.
Email: Support@skillfinlearning.com
Mobile: 9650010925
XNPV –(2/3)
XNPV SYNTAX = XNPV(rate, Values, Dates)
IMPORTANT POINTS
Rate is the discount rate to be applied to the cash flows
• XNPV does not discount the initial cash flows.
Values are the cash flows. Negative cash flows are considered as outflows and positive cash flows are considered as inflows by excel.
• Dates should be in an excel valid format.
Dates are the dates corresponding to the cash flows.
Email: Support@skillfinlearning.com
Mobile: 9650010925
• Dates should be in chronological order. • The range for values and dates should be in sync.
XNPV – (3/3) EXAMPLE John is considering the financial viability of an investment. The cash flows are as follows.
To calculate XNPV Type = XNPV(rate, Values, Dates) = XNPV(D11,D4:D9,C4:C9)
We get the XNPV of $ 21,247.26 for the investment. That means the investment is acceptable as the present value of cash inflows is greater than present value of cash outflows. Email: Support@skillfinlearning.com
Mobile: 9650010925
Internal Rate of Return (IRR) –(1/2)
• IRR is the internal rate of return for a series of cash flows. • Internal rate of return is the rate at which present value of cash inflows is equal to the present value of cash outflows (ie, rate at which NPV of the cash flows is Zero). • IRR is used to analyze potential investment decisions. IRR > Cost of capital; Accept project IRR< Cost of capital; Reject project Email: Support@skillfinlearning.com
IRR SYNTAX = IRR (Values, [Guess]) • Values are the cash flows. It is an array or reference to cells that contain the cash flows. Negative cash flows are considered as outflows and positive cash flows are considered as inflows by excel. • Guess is the number that is close to the result of IRR. It is not mandatory to put the Guess while calculating IRR.
Mobile: 9650010925
Internal Rate of Return (IRR) â&#x20AC;&#x201C;(2/2) EXAMPLE Mr. X is considering the financial viability of an investment. The cash flows are as follows. You are required to calculate the IRR of the investment.
To calculate IRR Type = IRR(Values, [Guess]) = IRR(D4:D9)
Press Enter. You get IRR of 18.37%. Since IRR is greater than the cost of capital. The project can be accepted. Email: Support@skillfinlearning.com
Mobile: 9650010925
XIRR – (1/2) • XIRR also known as Extended internal rate of return. It is an advance version of the IRR function.
• It is used to calculate the internal rate of return of stream of cash flows occurring during different time period. • It is commonly used while analyzing returns of investments made during different time period.
XIRR > Cost of capital; Accept project XIRR< Cost of capital; Reject project Email: Support@skillfinlearning.com
XIRR SYNTAX = XIRR (Values,Dates [Guess]) • Values are the cash flows. It is an array or reference to cells that contain the cash flows. Negative cash flows are considered as outflows and positive cash flows are considered as inflows by excel. • Dates are the dates corresponding to the cash flows.
• Guess is the number that is close to the result of XIRR. It is not mandatory to put the Guess while calculating IRR.
Mobile: 9650010925
XIRR â&#x20AC;&#x201C; (2/2) EXAMPLE X is investing in a Mutual fund at different time periods. On 31st August 2020, he redeemed all his investments. He wants to calculate the rate of return on his investments.
To calculate XIRR, Type, =XIRR(Values,Dates,[guess]) = XIRR(D4:D9,C4:C9)
Press Enter. You get XIRR of 16.5%. Return on investment is 16.5% for Mr. X
Email: Support@skillfinlearning.com
Mobile: 9650010925
MIRR – (1/2) • Modified internal rate of return or MIRR is a modified version of IRR. • IRR is calculated on an assumption that cash flows incurred during the lifetime of a project are reinvested at the internal rate of return. • This assumption may not hold true at times. • In Modified IRR the user has the option to enter the financing rate and the reinvestment rate. • Thus MIRR solves some of the problems/ limitations with IRR. Email: Support@skillfinlearning.com
MIRR SYNTAX= MIRR(Values, Finance_rate, Reinvest_rate)
• Values is the reference to series of cash flows from a project. Negative cash flows are considered as cash outflows, while positive cash flows are considered as cash inflows.
• Finance_rate is the interest rate paid on the investment. • Reinvest_rate is the interest rate received on cash flow which are reinvested.
Mobile: 9650010925
MIRR â&#x20AC;&#x201C; (2/2) EXAMPLE Mr. X is considering the financial viability of an investment. The cash flows are as follows. You are required to calculate the MIRR of the investment.
To calculate MIRR, type = MIRR(Values,finance_rate,Reinvest_rate) =MIRR(D4:D9,D11,D12)
Press Enter. You get XIRR of 17%. This means that the MIRR from the investment is 17%, which is well above cost of capital. Hence, the investment proposal can be accepted Email: Support@skillfinlearning.com
Mobile: 9650010925
PMT â&#x20AC;&#x201C; (1/2) â&#x20AC;˘ The PMT or payment function helps calculate the loan EMI. PMT SYNTAX = PMT(Rate,Nper,PV,[FV],[type]) Rate is the interest rate for the loan.
Nper is the total no. of instalments. PV is the present value of the loan FV is the future value of the loan after the last payment is made. Type is the logical value; you should enter 1 if the EMI payment is at the beginning of the period, or 0 if the EMI payment is at the end of the period. You can also leave it blank. In that case it will be considered as 0 by excel. Email: Support@skillfinlearning.com
Mobile: 9650010925
PMT â&#x20AC;&#x201C; (2/2) EXAMPLE To calculate the EMI type, = PMT(rate,nper,PV,FV,Type)
Mr. K is considering to take a home loan of $100,000 for 10 years. The interest rate is 7%. He wants to know the EMI on the loan amount.
=PMT(D5/12,D6*12,D4,D7,0)
Press Enter. You get PMT of $1,161,08. This means that Mr. K would have to pay $1,161.08 for 10 years (ie, 120 months) to repay the loan amount.
Email: Support@skillfinlearning.com
Mobile: 9650010925
NPER – (1/2) • NPER calculates the number of periodic payments (instalments) to be made if the rate of interest remains constant and the opening and closing balances are known. • This is generally used to calculate the Tenure of the Loan EMI, if the interest rate, Principal loan amount and outstanding balance at the end of the period and monthly payment is known.
NPER SYNTAX = NPER(Rate,PMT,PV,[FV],[type]) • Rate is the interest rate for the loan. • PMT is the periodic cash outflow. • PV is the present value of the loan • FV is the future value of the loan after the last payment is made. Email: Support@skillfinlearning.com
• Type is the logical value; you should enter 1 if the EMI payment is at the beginning of the period, or 0 if the EMI payment is at the end of the period. You can also leave it blank. In that case it will be considered as 0 by excel.
Mobile: 9650010925
NPER â&#x20AC;&#x201C; (2/2) EXAMPLE Mr. K is considering to take a home loan of $100,000. The interest rate is 7%. He can pay $1000 as EMI. What will be the loan Tenure.
To calculate the period of instalments type, =Nper(Rate,PMT,PV,[FV],Type) =Nper(D5/12,D6,D4,D7,0)
NPER = 150.52 months When you divide it by 12, you get 12.54 years. That means that Mr. K has to pay $1000 for 12.54 years to repay the loan amount of $100,000, if the interest rate remains constant at 7%. Email: Support@skillfinlearning.com
Mobile: 9650010925
Rate – (1/2) • Rate function calculates the rate of interest on the loan or investment. RATE SYNTAX = Rate(NPER,PMT,PV,[FV],[Type])
• NPER is the total no. of instalments. • PMT is the periodic payment made. This amount remains constant during the tenure of the loan or investment. • PV is the present value of the loan • FV is the future value of the loan after the last payment is made. • Type is the logical value; you should enter 1 if the EMI payment is at the beginning of the period, or 0 if the EMI payment is at the end of the period. You can also leave it blank. In that case it will be considered as 0 by excel. Email: Support@skillfinlearning.com
Mobile: 9650010925
RATE â&#x20AC;&#x201C; (2/2) EXAMPLE John is considering to take a home loan of $100,000. He has been asked to pay an EMI of $1,161 for 10 years. What is the interest rate on the loan?
To calculate the rate, type =rate(NPER,PMT,PV,[FV],[Type]) =Rate(D5*12,D6,D4,D7,0)
Press Enter. You get Rate of 0.58%. This is the monthly rate. When you multiply it by 12, you get 7%. This is the annual rate of interest on the loan for John.
Email: Support@skillfinlearning.com
Mobile: 9650010925
PPMT – (1/2) • PPMT function returns the principal payment for a given loan based on periodic constant payments and constant interest rate. PPMT SYNTAX = PPMT(Rate, Per, Nper, PV, [FV],[Type]) • Rate is the interest rate for the loan. • Per specifies the period and must be between 1 and NPER. • NPER is the total no. of instalments.
• PV is the present value of the loan • FV is the future value of the loan after the last payment is made. • Type is the logical value; you should enter 1 if the EMI payment is at the beginning of the period, or 0 if the EMI payment is at the end of the period. You can also leave it blank. In that case it will be considered as 0 by excel. Email: Support@skillfinlearning.com
Mobile: 9650010925
PPMTâ&#x20AC;&#x201C; (2/2) EXAMPLE X is taking a loan of $100,000 at 7% interest per annum. He wants to repay it in 3 equal yearly. Instalments. The annual payment is $38,105 Calculate the Principal repayment in year 1, 2 and 3.
To calculate the PPMT, type = PPMT(Rate, Per, Nper, PV, [FV],[Type]) =PPMT($D$5,$C11,$D$6,$D$4,$D$7,0)
Press Enter. You get $31,105.17 as Principal payment for year 1. If you Copy and Paste this formula to cells D12 and D13, you get the principal payment for year 2 and 3 as well. Email: Support@skillfinlearning.com
Mobile: 9650010925
IPMT – (1/2) • IPMT function returns the interest payment for a given loan based on periodic constant payments and constant interest rate. IPMT SYNTAX = IPMT(Rate,Per,Nper,PV,[FV],[Type]) • Rate is the interest rate for the loan. • Per specifies the period and must be between 1 and NPER. • NPER is the total no. of instalments.
• PV is the present value of the loan • FV is the future value of the loan after the last payment is made. • Type is the logical value; you should enter 1 if the EMI payment is at the beginning of the period, or 0 if the EMI payment is at the end of the period. You can also leave it blank. In that case it will be considered as 0 by excel. Email: Support@skillfinlearning.com
Mobile: 9650010925
PPMTâ&#x20AC;&#x201C; (2/2) EXAMPLE Taking the Previous Example. X is taking a loan of $100,000 at 7% interest per annum. He wants to repay it in 3 equal yearly Instalments. The annual payment is $38,105. Calculate the Interest repayment in year 1, 2 and 3.
To Calculate IPMT type, = IPMT(Rate,Per,Nper,PV,[FV],[Type]) =IPMT($D$5,$B11,$D$6,$D$4,$D$7,0)
Press Enter. You get IPMT of $7,000/- for Year 1. If you Copy and Paste this formula to cells D12 and D13, you get the Interest payment for year 2 and 3 as well. Note that total PPMT and IPMT is Equal to PMT which is $38,105 as calculated above. Email: Support@skillfinlearning.com
Mobile: 9650010925
Want to learn more? Explore our “Data Analysts Skills Training” (DAST) course Go from beginner to an Expert in Data analytics Skills in Excel. Join this program to build your Excel analytical skill quickly through hands on project Skills you will master: Data analytics functionalities in Excel Data visualization through charts Logical analysis driven decision-making Data cleaning and improvisation methods Pivot Table analysis Professionally format datasheets Excel tips and tricks using shortcuts Excel skills for business purposes Apply data analysis on real life datasets
Coaching includes: Annual (1 year) Subscription 100+ Live Interactive Sessions Unlimited Access to Video Content Weekly Mentorship Assistance Individual Assignments Evaluation Access on Mobile and Tablet also Certificate of Completion For more details please visit Data Analyst Skills Training (DAST)
Email: Support@skillfinlearning.com
Mobile: 9650010925
Want to learn more? Explore our “Financial Analysts Skills Training” (FAST) course Master the complete financial analytics and Excel modeling skills to get trained as a Financial analyst by an Expert instructor Includes: Annual (1 year) access 100+ Live interactions Direct access to the instructor Weekly mentorship assistance 10 hours on-demand HD videos 7 real life case illustrations 7 Practice assignment Access on Mobile and Tablet Certificate of Completion
Skills you will Master Build business and financial models Financial Accounting Financial ratios analysis Forecasting financial performance Scenario based modeling Business valuation techniques DCF based company valuation Multiples based company valuation Corporate Finance principles
For more details please visit Email: Support@skillfinlearning.com
Mobile: 9650010925
Financial Analyst Skills Training (FAST)
Our Training approach ensures You Learn Effectively and in Less Time
Email: Support@skillfinlearning.com
Mobile: 9650010925
THANK YOU
Email: Support@skillfinlearning.com
Mobile: 9650010925
Visit us: www.skillfinlearning.com Chat with us: +91 96500 10925 Email us: support@skillfinlearning.com