Anurag Singal - MS Excel, Second Edition: Lets Advance to the Next Level

MS ExcelMS ExcelLets Advance to the Next Level Second Edition Anurag Singal MS Excel, Second Edition: Lets Advance to the Next Level Copyright Business Expert Press, LLC, 2019. All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any meanselectronic, mechanical, photocopy, recording, or any other except for brief quotations, not to exceed 250 words, without the prior permission of the publisher. First published in 2019 by Business Expert Press, LLC 222 East 46th Street, New York, NY 10017 www.businessexpertpress.com ISBN-13: 978-1-94944-382-0 (paperback) ISBN-13: 978-1-94944-383-7 (e-book) Business Expert Press Quantitative Approaches to Decision Making Collection Collection ISSN: 2153-9515 (print) Collection ISSN: 2163-9582 (electronic) Cover and interior design by S4Carlisle Publishing Services Private Ltd., Chennai, India First edition: 2017
Second edition: 2019 10 9 8 7 6 5 4 3 2 1 Printed in the United States of America. MS Excel is one of the most powerful tools available to a business manager. In this book, I have made an attempt to move to the advanced level of skill sets and bring actionable insights to the user.

Hence, the material in this version has been organized as follows: A) Financial functions B) Conditional math and statistical functions C) Data analysis D) Decision making E) Data cleaning and use of macros F) Auditors The objective is to give you a flavor of how the vast array of functions can be used to make life easier and more efficient. You can achieve some amazing results by mastering Excel at a basic level. Do not just read this book as if you are reading a novel. Execute the given functions on a workbook simultaneously and experience the journey. It is those who practice it who will find the learning curve the steepest.

MS Excel; MS Excel Advanced tutorial; MS Excel Advanced courses; Advanced MS Excel for Accountants; Advanced MS Excel for Finance Managers; Microsoft Excel Books; Advanced Excel Books; Microsoft Excel Bible: The Comprehensive Tutorial Resource; Microsoft Excel - Data Analysis and Business Modeling; Advance Excel in Depth The operational efficiency of any knowledge professional can improve significantly with an upgradation of his or her skills of MS Excel.

With every additional function that you learn, life can become a lot easier. While in the first edition we had focused on the very basics, a common feedback was that readers wanted a more actionable book. Hence, the material in this version has been organized as follows: A) Financial functions B) Conditional math and statistical functions C) Data analysis D) Decision making E) Data cleaning and use of macros F) Auditors I hope this will significantly enhance the utility of the book. Dont just read this book like a novel. Practice it and the learning curve will be the steepest. The proof of the pudding lies in the eating.

So let the pages make their sales pitch themselves.

The first edition of this book was received with great interest by readers. It focused on providing users with a first-hand experience of the nuances of MS Excel. A lot of them have generously taken time out to provide feedback. I wish to express my special thanks to the reviewer, Sonia Singal, my better half. I also greatly appreciate the strategic comments and advice of Ayush Agarwal, who diligently and generously provided extensive comments as well as encouragement.

Scott Isenberg, Managing Executive Editor, and Charlene Kronstedt, Director of Production, Business Expert Press, have been a pillar of support. Rene Caroline provided excellent copyediting and book production. PV and FV Excel has several financial functions: A) The PV (Present Value) function calculates the total present (current) value of an investment with a fixed rate, specified number of payment periods, and a series of identical payments that will be made in the future. B) The FV (Future Values) function calculates the future value of an investment, given a fixed interest rate, term, and identical periodic payments. C) The NPV (Net Present Value) function calculates the net present value of an investment, given a fixed rate (rate of return) and future payments that may be identical or different. Internal Rate of Return (IRR) This returns the internal rate of return for a series of cash flows represented by the numbers in values.

These cash flows do not have to be even, as they would be for an annuity. However, the cash flows must occur at regular intervals, such as monthly or annually. The internal rate of return is the interest rate received for an investment consisting of payments (negative values) and income (positive values) that occur at regular periods. Determine # Periods in Bond Problem Yield to Maturity NPER and RATE The NPER (Number of PERiods) function calculates the number of payment periods for an investment or loan given a fixed interest rate, periodic payment, and present value. The RATE function calculates the periodic rate for an investment or loan given the number of payment periods, a fixed periodic payment, and present value. This slide shows specific examples of how the number of periods (NPER) and rate (RATE functions) have been applied in a worksheet.

Create a Loan Amortization Table Suppose you want to create a payment schedule that shows the interest per payment period, principal repayment for each payment, and the balance after each payment is made. This is called a loan amortization table, and a partial table is shown in the slide. Because this example is for a car loan over 4 years or 48 monthly payments, the complete schedule cannot be viewed in its entirety. Lets look at the financial functions that are needed to generate this amortization table. IPMT The IPMT function calculates the periodic interest for a specified payment period on a loan or an investment given a fixed interest rate, specified term, and identical periodic payments. =IPMT(rate,per,nper,pv,[fv],[type]) The arguments for these two functions are: A) The rate argument is the periodic interest rate. =IPMT(rate,per,nper,pv,[fv],[type]) The arguments for these two functions are: A) The rate argument is the periodic interest rate.

If the APR is 6 percent and the payments are made monthly, then the rate is 6 percent/12 or 0.5 percent. B) The per argument is the specific payment or investment period to use to calculate the interest where the first payment period is 1. C) The nper argument represents the total number of payment or investment periods. With a 4-year loan consisting of monthly payments, the number of payment periods is 48. D) The pv argument represents the present value of the loan or investment. E) The optional fv argument represents the future value of the loan or investment.