Monday, April 11, 2011

Excel Functions for HP12c

Do you use the Hewlett Packard HP12c financial calculator? Most people in the financial or real estate fields have had to use the HP12c one time or another. It is a very powerful tool and has been around for a while now. I feel that it takes some real effort to master all the steps needed to go through to get your answer. Once I have an answer I would like to do some "What-ifs" scenarios. Now comes the real problem. There is no easy way to just plug in a single number and determine how the outcome changes. Also, I can't print out a report showing my inputs and the results. With computers getting smaller and Microsoft Excel being installed on almost every Window based computer and Windows for Mac available now for Apple computers, I would rather use Excel than my HP12c or any other financial calculator. Excel functions such as PV (present value), FV (future value) and PMT (payment) are easy to use and along with RATE, CUMPRINC, CUMIPMT, IRR, NPV and making a few tables you find yourself with a very powerful tool to solve almost all your investment questions. Sometimes knowing what to put into the function can be a little frustrating. Take PV for example. The PV formula is PV(rate,nper,pmt,fv,type). When you want to find the present value of an investment that is worth, let's say $1,000 in a year from now, you have to know what the interest rate is, how many coupon payments you will receive, what the future value is and what type of payment it is. Whatever that means. The interest rate will go in the rate position, the nper is the number of coupons you will be paid in-between the time you buy the investment and the time it matures. So if you are getting the interest payment every month, you would count how many months you will receive this amount and that number would go into the nper space. But what happens if you don't get paid any interest or coupon payments in-between now and maturity? Then the nper is 1. Now pmt is the amount you would be paid on the coupon or the interest payment you would receive on a regular bases. If you only get paid at maturity and receive no coupon payment then you would put 0 in for pmt. In this example the fv is $1,000 and the type can be left blank. If you put a number in for the type it is either 1 or 0. An input of 0 means that you get your payment at the end of the period and the input of 1 means you get paid at the beginning of a period. Think of it like car payments or insurance. You have to pay before you get to use it. Unlike house payments where you are paying in arrears, meaning that you live in your house and then at the end of the month you pay for the mortgage. Rent on an apartment is mostly paid in advance. Wow, now that's a lot of stuff to remember when you just want to have an easy answer to how much money do I have to invest today to have $1,000 in a year. If you are trying to find the present value of an investment with no payment and is due in one year, why not just take the future value and divide by 1+the interest rate. For this easy equation that might be enough. But if you are getting paid coupons every month and the investment does not mature for several years then Excel makes it easier than using the HP12c or by doing the math by hand. The nice thing about Excel is that you don't have to redo the formulas every time you want to find the answer and doing "what-ifs" are as easy as changing one of the numbers. You can set up a Template and use it over and over, and you can copy the formula from one spreadsheet to another. Plus you can print out your results. Once you set up a spreadsheet you can use it to solve your repetitive questions fast and easy. I and a few friends have written an eBook on the most popular real estate investment formulas and functions that are in the HP12c manual. In the book we show you the formulas so you can apply them to any of your personal Excel workbooks. We also show you the inputs for the HP12c calculator. Go to for more information.