OpenOffice Calc Tips

December 27, 2005

Financial Functions: Creating a Payment Chart

One of the most useful and widely used financial functions available in OpenOffice Calc is PMT. We have discussed this before.

It's use is illustrated below in a simple example showing how to calculate the payments on a loan with fixed interest rate over a specified repayment term.



The advantage of using a spreadsheet in this manner is that we can plug different parameters into the equation and instantly see how it affects the payment we would need to make.

I will now extend this capability by showing how to create a table of payments for a range of loan amounts and interest rates. We first set up a blank table with the desired loan amounts on the side and the interest rates along the top.

In cell G4, we enter the following formula
=PMT(G$2/12;$C$6;-$F3;0)



We have already used this technique to create a multiplication chart here

Using the 'hybrid' addressing mode results in OOo Calc generating the correct results when we autofill the formulae down the first column. More information on the autofill mechanism here.



Likewise, we can autofill the complete table by dragging the first column across with the fill handle as shown below.



The above example spreadsheet can be downloaded here (ODF format)

Posted by Dave at 12:04 PM | Comments (0)

December 03, 2005

Financial Functions : Balloon Loan Example

A balloon mortgage is a short loan, lasting five to seven years, but the payments are based on a term of 30 years. Balloon loans often have a lower interest rate, and can usually be easier to qualify for than the traditional 30 year mortgage. At the end of your loan term you will need to pay off your outstanding balance. This usually means a refinance, a home sale or a conversion to a traditional mortgage at the current interest rates.

In the spreadsheet below, we use the PMT and FV functions to calculate the payments and final payment for a 7 year 8% balloon loan for $100,000.



Let us now revise this worksheet with a "what if" scenario - so easy with OOo Calc. let us sweeten the payment each month and see how it impacts our final payment due.



The worksheet for this example can be downloaded here (OO0 2.0)

Posted by Dave at 04:24 PM | Comments (0)

April 02, 2005

Financial Functions: CUMIPMT & CUMPRINC

A useful quantity to know for a given loan is the total interest payable over the history of the loan - or the total interest paid to a certain point. There exists a built in OOo Calc function specifically for that purpose - CUMIPMT. A companion function - CUMPRINC calculates the total principal paid to a certain point.

The sample spreadsheet below illustrates the use of CUMIPMT and CUMPRINC

D3:D6 are the input parameters. Change any of these values to examine the effect on the results.

For this example, CUMIPMT and CUMPRINC are calculated for the last payment of the loan - and as expected, CUMPRINC equals the loan amount. Also, the total payments over the histoy of the loan is just the sum of CUMIPMT and CUMPRINC



The next example shows how CUMIPMT and CUMPRINC can be calculated at any point during the loan. The formulae in C13:F13 are

=-IPMT($D$8;B13;$D$7;$D$2)
=-PPMT($D$8;B13;$D$7;$D$2)
=-CUMIPMT($D$8;$D$7;$D$2;1;B13;0)
=-CUMPRINC($D$8;$D$7;$D$2;1;B13;0)

These formula can be dragged down to fill the remaining rows.

Note that without the "-" in front of the above formulae, you would see negative numbers - a correct reflection of the direction of flow of funds.



The above sheet can be downloaded here (OOo 2.0 beta)

Posted by Dave at 03:49 PM | Comments (1)

April 01, 2005

Financial Functions: Calculating Principal & Interest

In this tip, we show how to determine the Principal and Interest components of a loan repayment using the built in OOo Calc functions IPMT and PPMT

For a standard loan or mortgage with fixed interest rate over the lifetime of the loan, a single regular payment can be calculated with the PMT function. This has been discussed in a previous tip.

However, as the amount of the outstanding balance is reduced over time, the Principal and Interest components of the loan change.

OOo Calc provides two functions that calculate the Interest and Principal components of any loan payment.

The arguments to the functions are : IPMT(rate, per, nper, pv, fv, type) and PPMT(rate, per, nper, pv, fv, type) where

• rate The interest rate for each of the time periods in the nper argument.
• per The number of the payment period. The first payment is 1 and the last is nper.
• nper The number of time periods between now and the end of the loan. For a standard 30-year mortgage - with monthly payments - this is 360.
• pv The present value (or initial value) of the loan. Also known as the principal fv The future value of the loan (desired) at the end of the nper payment periods. For mortgage payment calculations, this would normally be 0.
• type Optional argument which controls whether payments are made at the start of a period or the end.

In the example below, the loan parameters are defined in C3:C5 and the loan payment in C6 is calculated as =-PMT(C3;C4;C5)

The Principal component formula in C9 is calculated as =-PPMT($C$3;B9;$C$4;$C$5) and the Interest component in D9 is calculated as =-IPMT($C$3;B9;$C$4;$C$5) For subsequent periods, the only argument to these functions that changes is the period number.

In case you need further convincing, the IPMT and PPMT components are summed in column E, and are constant throughout the life of the loan - as expected.

Posted by Dave at 09:59 PM | Comments (1)

February 12, 2005

Rounding Numbers to Thousands and Millions

Often, in financial statements, we wish to express totals as 'Thousands' or 'Millions'. In Calc, we use custom formatting to do this.

In the example below, we use the number 1234567 to illustrate how the custom formats affect how this is displayed.

Firstly, #,## controls the use of commas. Secondly, 0 controls the number of digits after the decimal point. Finally, the trailing commas control the rounding of the number to thousands and millions.

Posted by Dave at 04:35 AM | Comments (0)

November 08, 2004

Webquery: Scraping FOREX quotes from Yahoo

As a follow on from the previous tip, I show how to grab FOREX quotes from the Yahoo Finance website.

Here is the link to the Yahoo FOREX quote for the US dollar against the Japanese yen. A screen shot is shown below. At any given time, we are interested in two values for each currency pair, the bid and the ask.

The URL in this example is http://finance.yahoo.com/q?s=USDJPY=X and the table is HTML__yfncsumtab.





By selecting Edit - Links, you will get the Links popup editor.



With this, you canreview and modify existing Webquery links in your spreadsheet.

Posted by Dave at 04:07 AM | Comments (0)

November 05, 2004

Webquery: Scraping stock quotes from MSN

In a previous entry we imported data from an HTML file on the local disk. In this example, we will extract stock quotes from an MSN site and import the data into OOo Calc spreadsheet.

We will import the stock quote information from this MSN webpage. A screen shot is shown below.



For this example, we import the data into the spreadsheet as before with Insert - External Data . The URL is http://moneycentral.msn.com/scripts/webquote.dll?ipage=qd&Symbol=INTC and the table we need to select is HTML_15.

You also need to make sure your proxy server settings are correct under Tools - Options (Internet)

Now, we can choose to have OOo Calc import this data at predefined regular intervals. This is useful - given the constantly changing nature of the stock prices. Also, each time that the user opens the document, OOo Calc will prompt to update the external links. Basically, what we have created is a 'window' to an external document. It can be a snapshot - or it can regularly monitor the target - updating accordingly.



Having extracted the data from the external website in it's'raw' form, we can use VLOOKUP to grab any piece of data we want. This is shown below.

Posted by Dave at 03:30 AM | Comments (6)

October 07, 2004

Financial Functions 3 : Complex Accumulation

In this example, we consider a more complex accumulation - wherein we are making regular payments as well as earning interest on our initial investment.

Here, we define names for the cells containing the main variables - so that the formulas are more readable.



The purpose of the month-by-month breakdown of the account also serves to confirm the accuracy of our original formula.

Posted by Dave at 05:55 AM | Comments (1)

October 06, 2004

Financial Functions 2: Accumulation

As a follow on from the mortgage example earlier, we now consider a small amount of money invested over a number of years at a fixed rate of interest.

The function we will use will be FV(rate, nper. pmt, pv)

Posted by Dave at 02:36 PM | Comments (0)

October 01, 2004

Financial Functions 1 : Mortgage calculation

At some point, you will want to use OOo Calc to perform some sort of financial analysis. Like other topics I cover, you could write a whole book on this. As an introduction to the topic of financial functions in OOo Calc, we will look at a home mortgage example. The PMT function calculates the regular payments needed to pay off a specified loan at a specified interest rate in a specified number of payments periods. The arguments to the function are :

PMT(rate, nper, pv, fv, type) where

• rate The interest rate for each of the tune periods in the nper argument. (see below)
• nper The number of time periods between now and the end of the loan. For a standard 30-year ,ortgage - with monthy paymnets - this is 360.
• pv The present value (or initial value) of the loan. Also known as the principal fv The future value of the loan (desired) at the end of the nper payment periods. For mortgage payment calculations, this would normally be 0.
• type Optional argument which controls whether payments are made at the start of a period or the end.

In the example below, we have a 30-year mortgage, $100k to be paid off monthly at an APR of 8%.



The monthly payment on this loan turns out to be $733.76. What we would also like to know is how our principal decreases over time. Each monthly payment is split between interest and principal. Early on in the repayment schedule, a greater percentage of the payment is set aside for interest.

The formula for calculating the principal balance at the end of each period (D10:D369) is as follows.

=FV(rate; nper; pmt; pv)

In this case, we are calculating the future value of loan one time period in the future, based on the present loan value and interest rate.

Posted by Dave at 09:34 PM | Comments (2)