**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

* 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.

i like this lesson. Tks!

ReplyDeleteHi Eddie,

ReplyDeleteI have been trying to compare strings with both cell contents and just INPUT commands; but the comparisons are failing. For example, I have a command string variable and simple if condition:

command := "Init";

if command == "Init" then

print("Got it");

end;

The print never is executed; so my comparison must be incorrect somehow. Is there a special way to test for string equality?

Hi Eddie,

ReplyDeleteIt was my misunderstanding of the INPUT command. When there is a list provided, the resultant variable becomes a CHOOSE field. So, the original variable was an integer value and not a string. The debugger just indicated a bad type and I didn't put the two together.

Love the work your doing. Finding it very difficult to find anything about programming the HP Prime. Would love to see you do a article on how to do a custom input form for the spreadsheet app with a next and back form button to navigate your input on form.

ReplyDelete