Build your own investment and financial calculator in microsoft excel

TMV, NPV and IRR

Build your own Investment and Financial Calculator in Microsoft Excel
http://spreadsheetml.com/financialmodeling

Copyright (c) 2009, ConnectCode All Rights Reserved. ConnectCode accepts no responsibility for any adverse affect that may result from undertaking our training. Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation. All other productnames are trademarks, registered trademarks, or service marks of their respective owners

Table of Contents
1. Introduction ........................................................................................................................... 1-1 1.1 Time Value of Money and Discounted Cash Flow Analysis ...................................... 1-1 1.1.1 Time Value of Money.................................................................................... 1-1 1.1.2 Discounted Cash Flow Analysis .................................................................... 1-1 1.2 Background ................................................................................................................ 1-1 Time Value of Money............................................................................................................ 2-2 2.1 Future Value of a Lump Sum ..................................................................................... 2-2 2.2 Future Value of a Lump Sum with more than 1 compounding periods per year ....... 2-2 2.3 Future Value of a Lump Sum with Continuous Compounding ................................... 2-3 2.4 Future Value of an OrdinaryAnnuity.......................................................................... 2-4 2.5 Future Value of a series of unequal cash flow ........................................................... 2-4 2.6 Present Value of a Lump Sum to be received in the Future ...................................... 2-5 2.7 Present Value of a Lump Sum with more than 1 compounding period per year ....... 2-5 2.8 Present Value ofan Ordinary Annuity........................................................................ 2-6 2.9 Present Value of a Perpetuity .................................................................................... 2-6 2.10 Present Value of a series of unequal cash flow ......................................................... 2-7 Discounted Cash Flow Analysis.......................................................................................... 3-8 3.1 Net Present Value (NPV) ........................................................................................... 3-8 3.2 Internal Rate of Return (IRR) ..................................................................................... 3-9 3.2.1 Money Weighted Rate ofReturn................................................................. 3-10 3.2.2 Time Weighted Rate of Return ................................................................... 3-10 3.3 Holding Period Return .............................................................................................. 3-11 3.4 Bank Discount Yield ................................................................................................. 3-11 3.5 Effective Annual Yield.............................................................................................. 3-11 3.6 Money Market Yield (CD equivalent yield) ............................................................... 3-11

2.

3.

Pg ii Investment and Financial Calculator Version 1.0

1.
1.1

Introduction

Time Value of Money and Discounted Cash Flow Analysis

The Time Value of Money andDiscounted Cash Flow Analysis are two cornerstone concepts of investment and financial analysis. The aim of this tutorial is to introduce these two concepts and at the same time use Microsoft Excel to develop financial models that utilize these concepts for analysing investments. It is known that most financial analysts in the industry developed their own set of models during their course of work...