HP Prime Spreadsheet App Tutorial 9: Custom Functions in the Spreadsheet App and the AMORT Function

HP Prime Spreadsheet App Tutorial 9:  Custom Functions in the Spreadsheet App and the AMORT Function

Lesson 9 Objectives:

* Defining a Custom Function

* Using a Custom Function in the Spreadsheet

* Using the AMORT Function

Background:

The AMORT function is found by pressing [ Menu ], 6.   It’s syntax is:

AMORT(range where you want your output, number of periods, annual interest rate, present value, payment, [payments per year (default is 12), grouping (default is 12), Beg (FALSE for end of period, TRUE for beginning of period payments), fix (default is 2)], [“Configuration”])

Anything enclosed in brackets [ ] are optional arguments.  I recommend that you type a single cell for the range of output, letting the Spreadsheet app worry about calculating the appropriate size of the amortization table.

Steps:

Setup:

1.     Clear the Spreadsheet.

2.    Select the cell with the hp logo.   Set the spreadsheet to Fixed format with 2 decimal places by using the (Format) command.

Create the Function:

3.     Press the [ Home ] key, then press [ Shift ], [x t θ n] (Define).  This takes you to the Define screen.  You will see two fields: Name and Function.  The order of the arguments take is taken from when they first appear in the Function.

4.    Name the function PYMT for payment.

5.    Type the formula in the Function field:  (-.01*R/C*P) / (1 – (1+.01*R/C)^-N).  Once you check it for accuracy, press (OK).

6.    You can test the function PYMT on the Home Screen.  PYMT(R,C,P,N) has four arguments:

·         R = annual interest rate

·         C = number of payments per year

·         P = present value

·         N = number of periods

Back to the Spreadsheet:

7.     Type the following labels:

·         A1 = “N”

·         B1 = “INT”

·         C1 = “PV”

·         D1 = “PMT”

8.    Type the following amounts and formulas:

·         A2 = 360  (for 360 payments)

·         B2 = 5  (for an annual interest rate of 5%)

·         C2 = 140000  (for the present rate of $140,000)

·         D2 = PYMT(B2,12,C2,A2)  (to calculate the payment, which should be -751.55)

9.    Go to cell A4.  Type =AMORT(A4,A2,B2,C2,D2,12)*.  An amortization chart is produced.

* There was an error pointed to me by Michael Kokot.  It is corrected now.  I thank you Kokot for bringing it to my attention.

Eddie 10/8/2015

This concludes Lesson 9.  Hope you find this helpful,

Eddie

This blog is property of Edward Shore, 2015.