Morten Helbæk - Financial Modelling and Asset Valuation with Excel

Financial Modelling and Asset Valuation with Excel

Finance is Excel! This book takes you straight into the fascinating world of Excel, the powerful tool for number crunching. In clear language it amalgamates financial theory with Excel, providing you with the skills you need to build financial models for private or professional use. A comprehensive knowledge of modelling in Excel is becoming increasingly important in a competitive labour market.

The chapters in , and take you in the end to topics such as array formulas, data tables and pivot tables. The other parts of the book discuss a variety of subjects such as net present value, internal rate of return, risk, portfolio theory, CAPM, VaR, project valuation, asset valuation, firm valuation, loan, leasing, stocks, bonds, options, simulation and sensitivity analysis.

The book is purposefully easy to understand. Thus it should be suitable both as a reference book and as a textbook for college/university courses such as Introduction to Finance and Investment Analysis and Financial Modelling with Excel. All subjects are illustrated by examples and accompanying spreadsheets. At the end of each chapter you will find a variety of problems. Spreadsheet solutions for all examples and problems are available on the books home page at www.routledge.com.

The book should be helpful for both professionals and students that want to improve their knowledge in finance and investment analysis and at the same time develop skills in building Excel models.

Morten Helbk is Associate Professor at North-Trndelag University College, Norway.

Ragnar Lvaas is Research and Development Engineer at Nortroll AS, Norway.

Jon Mjlhus is Senior Lecturer at Buskerud University College, Norway, and Economist at ODIN Forvaltning, one of Norways largest asset managers.

Financial Modelling and Asset Valuation with Excel

Morten Helbk, Ragnar Lvaas and Jon Mjlhus

First published 2013

by Routledge

2 Park Square, Milton Park, Abingdon, Oxon OX14 4RN

Simultaneously published in the USA and Canada

by Routledge

711 Third Avenue, New York, NY 10017

Routledge is an imprint of the Taylor & Francis Group, an informa business

2013 Morten Helbk, Ragnar Lvaas and Jon Mjlhus

The right of Morten Helbk, Ragnar Lvaas and Jon Mjlhus to be identified as authors of this work has been asserted by them in accordance with the Copyright, Designs and Patent Act 1988.

All rights reserved. No part of this book may be reprinted or reproduced or utilized in any form or by any electronic, mechanical, or other means, now known or hereafter invented, including photocopying and recording, or in any information storage or retrieval system, without permission in writing from the publishers.

Trademark notice: Product or corporate names may be trademarks or registered trademarks, and are used only for identification and explanation without intent to infringe.

British Library Cataloguing in Publication Data

A catalogue record for this book is available from the British Library

Library of Congress Cataloging-in-Publication Data

Helbk, Morten. Financial modelling with Excel / Morten Helbk, Ragnar Lvaas and Jon Mjlhus.

pages cm

1. FinanceMathematical modelsComputer programs. 2. Microsoft Excel (Computer file) I. Lvaas, Ragnar, 1950- II. Mjlhus, Jon. III. Title.

HG106.H45 2013

332.0285554dc23

2012044636

ISBN: 978-0-415-62596-8 (hbk)

ISBN: 978-0-415-63058-0 (pbk)

ISBN: 978-0-203-36288-4 (ebk)

Typeset in Times New Roman

by Cenveo Publisher Services

A formula in Excel

The ribbon in Excel 2010

The Excel Options dialogue box

Spreadsheet tabs and status bar

Arrange windows

The Object dialogue box

The Microsoft Equation editor

Equation tools in Excel 2010

Page Setup

Sales figures

Sorting data

Custom Lists

Comment

The Format Cells dialogue box

Conditional formatting

Data Validation

Calculation in Excel

Formula auditing

Circular reference warning

Financial statements

Financial statements

Function library

The Insert Function dialogue box

Autocomplete

The SUMPRODUCT function

Look up in a table

Addition

Addition with an array formula

Arrays

Arrays

A set of data and intervals

The FREQUENCY function

Frequency table

Solving a system of linear equations by matrix calculations

Column chart

Column chart

Column chart with two series

Unit costs diagram

The Edit Series dialogue box

Dialogue box for Sparklines

Sparklines

Excel table

Dialogue box for Pivot tables

Pivot table showing the total amount each customer has spent on each product

Pivot table

Pivot table

The Value Field Settings dialogue box

Pivot table

The Value Filter dialogue box

Filtering

The Slicer dialogue box

Pivot table with slicers

Report for sales assistant Olson

Pivot table with pivot chart

Pivot table with pivot chart

Pivot chart

Creating a two-dimensional data table

Data table

Creating a one-dimensional data table

One-dimensional data table

Creating a two-dimensional data table

Multiplication table

Goal Seek

Sensitivity analysis with Goal Seek

The Solver dialogue box

The Constraint dialogue box

Optimization problem

The Data Analysis dialogue box

F-test with the data analysis tool

Present value calculation

Present value calculation

Present value calculation

Present value calculation

Repayment plan

Calculation of number of periods

Calculation of effective interest

Net present value calculation

Present value profile

Internal rate of return calculation using the RATE function

Internal rate of return calculation using the IRR function

An annuity loan

Internal rate of return calculation using Goal Seek

The IRR dialogue box

Present value profile

Present value profiles

Calculation of payback period using Goal Seek

Calculation of payback period

Comparing equivalent annual incomes of different machines

Calculation of optimal lifespan for a machine

Calculation of standard deviation

Calculation of average returns and standard deviations

Calculation of covariance and correlation coefficient

Calculation of covariance

Results from a covariance calculation

Regression line

Covariance matrix for a portfolio of the two stocks A and B

Expected returns and standard deviations for different portfolios of A and B

Covariance matrix for a portfolio of the stocks 1, 2, 3, , N

Calculation of expected return and standard deviation

Calculation of expected return and standard deviation

Calculation of expected return and standard deviation

Calculation of standard deviation

Minimization of a portfolios standard deviation

Standard deviation calculations

Expected returns and standard deviations for portfolios of the stocks A, B and C

The efficient frontier

Calculations for the efficient frontier

The efficient frontier

Market portfolio

Shares of risk-free investment in a portfolio

Geometrical considerations

Calculation of a portfolios composition