Friday, September 25, 2015

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.



4 comments:

  1. Hi Eddie,

    I 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?

    ReplyDelete
  2. Hi Eddie,

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

    ReplyDelete
  3. 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

HP Prime and Casio fx-CG50: Basic Spreadsheet Comparison

HP Prime and Casio fx-CG50:  Basic Spreadsheet Comparison HP Prime Casio fx-CG50 Maximu...