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.

Calculus: the cooling constant of the casserole

Tutoring calculus or differential equations, Newton’s Law of Cooling will surface. The tutor looks at a real-life example.

In yesterday’s post I mention that a casserole dish taken out of the oven cooled from 177C to about 40C during one hour.

Newton’s Law of Cooling can be used to calculate the temp of a cooling object:

Tf = Tiekt

where

Tf = final temp

Ti = initial temp

k = the constant of cooling (if cooling, k will turn out negative)

t = time (usually in seconds)

For this case, we have t=3600 (3600s in one hour):

40 = 177ek3600

Dividing both sides by 177 gives

0.226=e3600k

Now we ln both sides:

ln0.226 = 3600k

Finally we divide by 3600:

-4.13×10-4 = k

Apparently the cooling constant of the casserole is -4.13×10-4.

Source:

Larson, Roland E. and Robert P. Hostetler. Calculus. Toronto: D.C. Heath and Company, 1989.

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

Math: ideas about infinity: how an infinite series can have a finite sum

Tutoring math, you realize that infinite series can be hard to visualize. The tutor offers perspective.

An infinite series is a sum of terms that never end. An example is a repeating decimal, such as 0.2222222….., which means

0.2222222…. = 0.2 + 0.02 + 0.002 + 0.0002 + 0.00002 + ….

Perhaps surprising, at first, is that a sum may be of infinitely many terms, but have finite value. Yet, most people will agree that

0.2222222…. < 0.3
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.

Math: how far away is the goose?

Tutoring math, you notice that people like relatable examples. The tutor brings up his observation of a Canada goose.

Looking out over a lake in Nanaimo on Sunday morning, I saw an exceptional Canada goose swimming apart from the others.

The setting was so tranquil, even distance seemed irrelevant: I felt that, on a whim, I could suddenly scoot down to the water, plunge in, and join the goose if I wanted. Yet, how far away was it, really?

Holding my phone at 20cm, or 200mm, I observed the goose at about one-sixth of my phone’s lens port, which is 7mm across. So, to me, the goose appeared 7/6=1.17mm. Yet, a goose that prosperous would likely be about 60cm, or 600mm, from tail to breast. From optics,

object distance/image distance = object length/image length

Therefore,

d/200 = 600/1.17

Multiplying both sides by 200, we get

d=200(600)/1.17 = 102574mm or about 103m

Source:

Bull, John and John Farrand, Jr. The Audubon Society Field Guide to North American Birds, eastern region. New York: Alfred A. Knopf, 1977.

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

Math: Excel: entering complex numbers

Tutoring differential equations or complex variables, you might use a spreadsheet sometimes. The tutor gives a hint about entering complex numbers on Excel.

Excel does, indeed, handle complex numbers. Formulas for them are among the Engineering ones.

Apparently, to enter a complex number, quotes are needed around it. For instance, to obtain the product of 6-i and 2+i, you would enter

=improduct(“6-i”,”2+i”)

Hopefully you receive the answer 13+4i.

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

Math: Excel: square roots of negative numbers

Tutoring college math, complex numbers will likely arise. The tutor mentions using Excel.

Square roots of negative numbers (aka, complex numbers or imaginary numbers) may not be encountered by many high school students. However, in college math they are used, and electricians also use them.

Excel will calculate the square root of a negative number, using the imsqrt() function. Example:

=imsqrt(-9)

gives, on this desktop, 1.8377E-16 + 3i.

1.8377E-16 is so tiny, of course, it’s equivalent to zero. So the effective answer is 3i, which is correct. An electrician might call it j3.

HTH:)

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

Calculator Usage: Euler’s identity with the Nexus 4’s on-board scientific

Tutoring math, calculators can always be interesting. The tutor shares a capability of the Nexus 4 on-board calculator.

If the Nexus 4 on-board calculator has broad complex number capabilities, I don’t know about them. I tried √(-4) and got Not a number.

Yet, behold!

Neat, eh?

Source:

Wikipedia

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