Chapter 7_M2

WORKING WITH FORMULAS AND FUNCTIONS CHAPTER - 7: USING FORMULAS FOR FINANCIAL ANALYSIS Structure 1.0 Learning Objectives 1.1 Introduction 1.2 Financial Analysis in MS Excel 1.3 Present Values of a series of Future Payments 1.4 What is EMI? 1.5 Summary 1.6 References 1.0 Learning Objectives After studying this unit, you will be able to:  Explain Financial Analysis in MS Excel?  Explain Present Values of a series of Future Payments  Explain What is EMI and how to calculate it? 1.1 Introduction Spreadsheets got their start in the accounting and finance departments back when it was all done with paper and pencil. And even though Excel has grown far beyond a simple electronic ledger sheet, it's still a required tool in business.yu In this chapter, you'll look at some formulas commonly used in accounting, finance, and other areas of businesses. Page 37 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS 1.2 Financial Analysis In EXCEL  Microsoft Excel gives to an user the whole toolkit for analyzing the financial performance of an enterprise, performing statistical calculations and forecasting.  Built-in functions, formulas, add-ins allow you to automate the lion's share of the work. Thanks to automation, an user needs only to substitute new data and on their basis automatically generated ready reports, which a lot of people make for hours.  You can perform financial analysis with Excel in an easy way. Excel provides you several financial functions such as PMT, PV, NPV, XNPV, IRR, MIRR, XIRR, and so on that enable you to quickly arrive at the financial analysis results. 1.3 Present Values of a series of Future Payments The present value is the total amount that a series of future payments is worth now. You can calculate the present value using the Excel functions −  PV − Calculates the present value of an investment by using an interest rate and a series of future payments (negative values) and income (positive values). At least one of the cash flows must be positive and at least one must be negative.  NPV − Calculates the net present value of an investment by using a discount rate and a series of periodic future payments (negative values) and income (positive values).  XNPV − Calculates the net present value for a schedule of cash flows that is not necessarily periodic. You can use Excel function PV: PV (rate, nper, pmt, [fv ], [type]) Page 38 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS 1.4 What is EMI?  An Equated Monthly Installment (EMI) is defined as \"A fixed payment amount made by a borrower to a lender at a specified date each calendar month. Equated monthly installments are used to pay off both interest and principal each month, so that over a specified number of years, the loan is paid off in full.\"  In Excel, you can calculate the EMI on a loan with the PMT function.  For example, Suppose, you want to take a home loan of 5000000 with an annual interest rate of 11.5% and the term of the loan for 25 years. You can find your EMI as follows − - Calculate interest rate per month (Interest Rate per Annum/12) - Calculate number of monthly payments (No. of years * 12) - Use PMT function to calculate EMI - You will get the following results: Page 39 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS 1.5 Summary  Spreadsheets got their start in the accounting and finance departments back when it was all done with paper and pencil.  Microsoft Excel gives to an user the whole toolkit for analyzing the financial performance of an enterprise, performing statistical calculations and forecasting.  You can perform financial analysis with Excel in an easy way. Excel provides you several financial functions such as PMT, PV, NPV, XNPV, IRR, MIRR, XIRR, and so on that enable you to quickly arrive at the financial analysis results.  An Equated Monthly Installment (EMI) is defined as \"A fixed payment amount made by a borrower to a lender at a specified date each calendar month. 1.6 References http://alqashi.com/book/book18.pdf https://www.oreilly.com/library/view/excel-2019- bible/9781119514787/c15.xhtml https://exceltable.com/en/analyses-reports/financial-analysis-example https://www.tutorialspoint.com/excel_data_analysis/excel_data_analysi s_financial.htm Page 40 of 63 All Rights Reserved. Vol. TLE001/03-2022