Spreadsheets: Excel: finding the monthly loan payment

Tutoring financial math, spreadsheets might be used. The tutor shows how to find the monthly payment on a loan using Excel.

In yesterday’s post I tell how to find the amount against the principal that a certain payment removes, using the ppmt function.

Perhaps more directly, a person might wonder how to calculate the monthly payment in the first place. Here’s how:

Example: Imagine a 25-year, $100,000 loan at 4% compounded monthly, with monthly payments as well. Find the monthly payment.

Solution: Using Excel, you’d enter

=pmt(4%/12, 300, 100000)

300 means the total number of payments: 12 per year for 25 years. The payment is assumed to be at month’s end.

Hopefully, the answer will come to -527.84, the negative meaning money paid out (as opposed to received).

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

Excel: payment against principal

Tutoring financial math, amortization arises. The tutor mentions the Excel function for it.

Example: For a 25-year loan of $100,000 at 4%, compounded monthly with monthly payments, what is the amount against the principal of the 101st payment?

Solution:

Using Excel, it would be =ppmt(4%/12, 101, 300, 100000), which gives -271.30 (meaning a reduction of 271.30 against the principal).

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

Financial math: what does amortization mean?

Tutoring math, definitions are needed. The tutor shares the definition of amortization.

Amortization schedules and amortization functions are commonly encountered in financial math. What does amortization mean?

amortization: the separation of a loan payment into two amounts:

  1. the payment against principal;
  2. the interest

Source:

Hewlett-Packard Business Calculator Owner’s Manual. Corvallis: Hewlett-Packard, 1988.

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

Math: what discount will exactly cancel the tax?

Tutoring high school math, you see financial word problems. The tutor gives an example.

Problem: At 12% tax, what discount will lead to paying exactly the sticker price after tax?

Solution

The final price, f, is as follows:

f = (1-discount)(1+tax)p

Where p is original price, and discount and tax are in decimals, not percent (eg, tax is 0.12 rather than 12%)

We need f=p, at tax=0.12:

p=(1-discount)(1+0.12)p

Dividing both sides by p we get

1=(1-discount)(1.12)

Then, dividing by 1.12,

0.8929 = 1-discount

Rearranging we get

discount = 1-0.8929 = 0.1071 or 10.71%.

Apparently, at 12% tax, a discount of 10.71% is needed to equate the after-tax price to the sticker price.

Cheers.

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

Calculator usage: finding final price after discount and tax with the HP-10B

Tutoring financial math, you might often use the HP-10B. The tutor shows how easily it can apply a discount then add tax to get the final price.

Example: Imagine a handbag is regular price $85 but is discounted by 20%. Assuming 12% sales tax, find final price using the HP-10B.

Solution:

  1. Key in 85
  2. Key in – 20 % =
  3. Key in + 12 % =

HTH:)

Source:

Hewlett-Packard HP-10B Business Calculator Owner’s Manual. Corvallis: Hewlett-Packard, 1994.

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

Calculator usage: markup on the HP-10B

Tutoring financial math, you might fall in love with a calculator. The tutor tells about markup on the HP-10B.

Example: Imagine you buy a product for $35, then want to apply a 25% markup. Find the tag price using the HP-10B.

Solution:

  1. Key in 35 then CST
  2. Key in 25 then the orange key then MAR
  3. Press PRC

HTH:)

Source:

Hewlett-Packard HP-10B Business Calculator Owner’s Manual. Corvallis: Hewlett-Packard, 1994.

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

Calculator usage: memory on the HP-10B

Tutoring financial math, you’ll likely encounter the capable HP-10B. The tutor tells how to use its user-accessible memory.

The HP-10B seems to have 11 dedicated places to store your own numbers. The locations are at 0 to 9, plus there is the M register.

Example: On the HP-10B, store the number 65.21 in register 5.

  1. Key in 65.21
  2. Press the orange key
  3. Press RCL
  4. Press 5

To retrieve the number,

  1. Press RCL
  2. Press 5

HTH:)

Source:

Hewlett-Packard Business Calculator HP-10B Owners Manual. Corvallis: Hewlett-Packard, 1994.

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

Business math: a word problem about market share.

The tutor works a word problem to find time until market share equalization.

Imagine a relatively new device, of which there are two competing versions, A and B. (We assume that no-one owns both.) The potential market is 100 million, but at present, 20 million own A, while 12 million own B. Currently, however, B outsells A 2:1. Combined sales total 600 000 per month.

Assuming only new sales (as opposed to replacement), and only one per customer, when will ownership of B equal A? How many of each will have sold by then? What will be the total market penetration?

Solution:

Let

x=new sales of A (which means 2x=new sales of B)
t=time

We want the owned units of A to equal the owned units of B

20 000 000 + x = 12 000 000 +2x

Subtracting x and also 12 000 000 from both sides gives

8 000 000 = x

Right now, 20 000 000 people own A. So when 8 000 000 more units of A have sold, 28 000 000 will own one. During that time, 16 million units of B will sell; 12 million people own B right now. 16 million + 12 million = 28 million, the same as A will be.

At market equalization, 28 000 000 customers will own each. Therefore, 56 000 000 will own one or the other: total market penetration will be 56%.

How long will market equalization take? Of the 600 000 units selling each month, 200 000 are A (while 400 000 are B). At 200 000 units per month, the time for 8 000 000 units to sell is 8 000 000 ÷ 200 000 = 40 months.

HTH:)

Source:

Tan, S.T. Applied Finite Mathematics. Boston: PWS-KENT, 1990.

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

HP-10B: present value

The tutor shows a present value example on the Hewlett Packard 10B business calculator.

Example: If $100 is received at month end for five years, at annual interest rate 3.2% compounded monthly, what is the total present value of the payments?

Solution:

  1. Press the key that’s an orange rectangle, then INPUT to clear all the financial values.
  2. Key the following: 100 PMT 3.2 I/YR 12 orange rectangle key PMT 0 FV 5 orange rectangle key N PV
  3. Hopefully you receive the answer -5537.80
  4. The negative sign just means the present value is “opposite” of the received payments: you’d have to pay 5537.80 now to receive the $100 payments, monthly for 5 years, at 3.2% compounded monthly.

HTH:)

Source: Hewlett-Packard Business Calculator Owner’s Manual, Edition 6. Corvallis: Hewlett-Packard, 1988.

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

Math of finance: bond pricing, featuring the TI BA-35

The tutor shows an example of how to price a bond.

Example:

Imagine the following scenario: a 20-yr $10K bond pays at 4% per year, compounded and paid semi-annually. However, realizing a present interest rate of only about 2.5%, a second buyer is happy to buy the bond for 3% yield. How much should the second buyer pay if they buy right after the interest payment at 5 years?

Solution:

Assuming the interest payment at exactly 5 years goes to the previous owner, the second buyer’s first interest payment will be at 5½ years (5 years, 6 months).

To find the purchase price, we find the present value of all the bond’s future interest payments, then its redeem value of $10K.

Interest payments:

At half-year intervals, the first being at 5½ years, the last at 20 years, there will be 30 payments total. One way of seeing it: the original purchaser’s first interest received was 6 months after purchase, their last at 5 years in. Therefore, they got 10 payments. The bond pays, in total, 40 payments: 2 per year for 20 years. Therefore, the second buyer gets the other 30 payments.

Each interest payment is $200: the interest is 4% annually, but paid and compounded semi-annually. Therefore, the interest per period is 4%/2 or 2%. 2%x10K=$200.

The second buyer expects only 3% annually, paid and compounded semi-annually. %i is 3/2=1.5.

Here are the inputs:

First, 2nd FRQ 2nd N to clear the financial registers.

0 FV
200 PMT
1.5 %i
30 N
CPT PV

Hopefully you get the answer 4803.1676

$10K redeem value:

Next, we find the present value of the $10K redeem value, payable 15 years from today:

10000 FV
0 PMT
1.5 %i
30 N
CPT PV

Hopefully you get the answer 6397.6243.

If the second buyer wants a 3% yield for this bond, they should pay 4803.1676 + 6397.6243 = 11200.79.

HTH:)

Source:

Killip, T. Brian. Mathematics for Business: the CGA Reference Handbook. Toronto: Harcourt Brace & Company, 1993.

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.