Personal Money Management Made Simple with MS Excel: How to save, invest and borrow wisely

INTRODUCTION

How much do you need to save each month in order to have, say, €500,000 when you retire? Is paying for your car on a monthly basis a good deal? Which bank gives you a better deal for your deposits? Are you paying too much on your loan? Is the minimum payment on your credit card a wise choice? Is it better to take you lottery winnings as a lump-sum or over 30 years?

This book will try to analyze such issues using a practical tool: Microsoft Excel®. It will provide simple templates whereby you can get the answers you need by simply changing the appropriate values.

I will assume that you are not reading this book for detailed information on how to make money in the stock, bond, derivative, commodity or foreign exchange markets since you can get all that information from specialized textbooks or qualified financial advisors. Instead, I will concentrate on the elements that will contribute into sound personal financial management: saving, borrowing and investing money wisely by making informed decisions. I’m sure that most of the information provided in this book is, one way or another, scattered all over the internet but isn’t it nice to have all you need, or almost all, in just one place?

Although finance and mathematics are intertwined, the use of mathematics is virtually absent since Excel’s® built-in financial functions simplify the process. Simple arithmetical operations are used, when necessary, to explain specific concepts.

I assume very basic knowledge of Microsoft Excel ® 2007 or 2010. If you don’t feel comfortable with Excel®, you can follow a very good series of twelve free YouTube tutorials in the following link:

http://www.youtube.com/watch?v=JfJpplCNe8w

It is very important to enter formulas correctly in the formula bar either directly or using the syntax of built-in functions. The first step in entering formulas and built-in functions is the equal sign: =. We will be using built-in functions almost predominantly.

In the figure below, the formula that calculates the sum of the values in cells B1, B2, B3 and B4 is entered in cell B5 as follows:

Step 1: Enter the equal sign: =

Step 2: Click on cell B1 and it will appear in the formula bar

Step 3: Enter the plus sign: +

Step 4: Click on cell B2 and it will appear in the formula bar.

Step 5: Enter the plus sign: +

Step 6: Click on cell B3 and it will appear in the formula bar

Step 7: Enter the plus sign: +

Step 8: Click on cell B4 and in the formula bar

Step 9: Press the Enter key

The number 700 will appear in cell B5.

Alternatively, we can calculate the sum of the values in cells B1, B2, B3 and B4 by using the SUM function.

Step 1: Enter the equal sign: =

Step 2: Type SUM or click the fx button in the formula bar and search for it

Step 3: Open parenthesis: (

Step 4: Select the cells B1, B2, B3, B4 with the mouse or write B1:B4.

Step 5: Close parenthesis: )

Step 6: Press the Enter key

The number 700 will appear again in cell B5. The figure below shows the process.

DEFINITIONS AND NOTATION

Present value (PV): the amount of money invested or borrowed at a certain interest rate and for a certain time period. It is also known as capital or principal (P) or present worth (PW).

Future value (FV): what an amount of money invested or borrowed is worth at a future date. If we borrow €2,000 today for three years, how much will we owe at that time? Similarly, if we deposit €5,000 into a bank account, how much will it be worth two years from now? It includes the PV and the interest earned or paid.

Interest rate (I, or rate in Excel syntax): the price paid for the use of money for a specific time period. The interest rate is expressed as a percentage but is often used in its decimal rather than its percentage form when performing calculations. To express the interest rate as a decimal, simply divide it by 100. A few examples:

A 10% interest rate: 0.10 (10/100)

A 5% interest rate: 0.05 (5/100)

A 15% interest rate: 0.15 (15/100)

Number of periods (n or nper in Excel syntax): the time period for which the money is borrowed or invested. It denotes how often interest is paid; it can be annually, semi-annually, quarterly, monthly, weekly, daily or continuously.

Cash flows: money payments; cash inflows (positive flows) represent money coming in while cash outflows (negative flows) refer to money going out. For the borrower, the present value (PV) is positive and the future value (FV) negative. For the lender, the PV is negative and the FV positive.

Currency: all the examples in the book use the euro as the default currency. However, this is only indicative as you can perform the same calculations using the dollar, the pound or any other currency by simply selecting the desired currency in the Format cell menu. In all probability, your version of Excel®, depending on where you live, will have the local currency as the default currency.

HOW MONEY IS RELATED TO TIME

Financial decision-making, whether you are a financial wizard or just an ordinary individual, involves the estimation of the money (or cash flows) expected to be received sometime in the future.

Money has time value. When investing money today, we expect a return on our investment. Having €1 today is worth more than €1 available tomorrow. That is, a specific amount of money today is equivalent to a larger