Loan calculations

[kay]

I know the formula to input the numbers in to calculate the monthly payment of a loan if given the interest rate, term and loan amount. But for the life of me, I can not figure it out manually. If someone takes out a 100,000 mortgage loan at 8% interest for 3 years to buy some land, how do you get the monthly payment. The payment should be 38,803.35, but I can not manually figure it out. Someone in school posed this question to me and it is driving me crazy. Can somone post the mathematical calculation for me? Thanks.

 

[Norman Harker]

Hi Kay!

Your 38803.35 was obtained from:

=PMT(8%,3,100000,0,0)
Returns: -38803.3514046328

The negative indicates a payment out in return for the original
receipt of the loan.

This is the annual payment in arrears for a loan of 100000 over 3
years at 8% interest. Excel "knows" nothing about the periodicity of
the payments.

Assuming that you have an 8% per annum nominal rate compounded monthly
(often called the APR) the formula you want is:

=PMT(8%/12,3*12,100000,0,0)
Returns: -3133.63654614311

If the 8% is an annual effective rate you need to convert it to
monthly effective using =(1+8%)^(1/12)-1

But if you are in the UK and you are dealing with a building Society
that uses the old system, you use

=38803.35/12

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

I know the formula to input the numbers in to calculate the monthly

payment of a loan if given the interest rate, term and loan amount.
But for the life of me, I can not figure it out manually. If someone
takes out a 100,000 mortgage loan at 8% interest for 3 years to buy
some land, how do you get the monthly payment. The payment should be
38,803.35, but I can not manually figure it out. Someone in school
posed this question to me and it is driving me crazy. Can somone post
the mathematical calculation for me? Thanks.

 

[JE McGimpsey]

I'm not understanding here:

A monthly payment of $38,803.35 for a 100,000 loan would pay off the
loan in less than three months, not three years.

The $38,803.35 payment would be for *annual* payments, not monthly:

=PMT(8%,3,-100000,0)

You always need to use units that are consistent as to time period. The
PMT function solution for monthly payments is

=PMT(8%/12, 3*12, -100000, 0)

which returns $3,133.64 for the monthly payment.

For the formula, see Help's "PV" topic.

 

[Norman Harker]

Hi Kay!

The basic repayment calculation without the financial function is:

=-100000/((1-(1+8%/12)^-(3*12))/(8%/12))

Excel's financial functions use a common formula:

PV + PMT * ((1-(1+RATE)^-Nper)/(Rate)) + FV*(1+RATE)^-Nper = 0

In this case the Future Value of a fully redeeming mortgage is 0 so we
get:

PV + PMT * ((1-(1+RATE)^-Nper)/(Rate)) + FV*(1+RATE)^-Nper = 0

PMT = -PV / ((1-(1+RATE)^-Nper)/(Rate)) + FV*(1+RATE)^-Nper



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[kay]

thanks for all of the replies. the payment IS annual. but how would I show it manually?, say on a calculator or trying to explain it to someone. I can't just multiply the % by the loan amout because that would only give me the interest. Do you see where I'm going?

 

[JE McGimpsey]

use the formula that Norman gave you.

 

[Norman Harker]

Hi Kay!

One minor correction to the formula (it was late at night!) that was
OK for your purposes but which ought to be provided.

The common formula used by Excel is:

PV + PMT * ((1-(1+RATE)^-Nper)/(RATE))*(1+RATE*Type) +
FV*(1+RATE)^-Nper = 0


I've multiply the payment element by (1+Rate*Type). Type is a
constrained variable that is 1 where payments are in advance and 0
where in arrears. If, as is normal with a loan, the first payment is
made 1 (eg) month after draw down, the (1*Rate*Type) resolves to 1 and
it "disappears" from that part of the expression.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Norman Harker]

Hi Kay!

Here's how I'd explain "manually" (he says avoiding all sorts of
temptations!)

Here's the repayment that I've calculated at 3133.64

Interest is 8%/12 per month or .6666667%

After the first month interest is 100000 * 0.6666667% = 666.67

I make a payment of 3133.64 and of that payment 666.67 is interest and
the balance of 2466.97 is repayment of the debt leaving me with
outstanding debt of 100000 - 2466.97 = 97533.03

After the second month interest is 97533.03 * 0.6666667% = 650.22

I make a payment of 3133.64 and of that payment 650.22 is interest and
the balance of 2483.42 is repayment of the debt leaving me with
outstanding debt of 97533.03 - 2466.97 = 95,066.06

This goes on for 36 months and at the end of that time I should find
that the outstanding debt is gone.

If you like, I'll send you an amortization table that uses precisely
this approach to show how a loan is paid off. Just apply to the email
below.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

thanks for all of the replies. the payment IS annual. but how would

I show it manually?, say on a calculator or trying to explain it to
someone. I can't just multiply the % by the loan amout because that
would only give me the interest. Do you see where I'm going?

 

[kay]

Hi Norman,

I understand the payments after the interest is taken out. I guess my question is basically, how do you come up with the 38,803.35 payment. The problem was for a $100,000 loan taken out at 8% for 3 years and to determine the annual payment. The answer is 38,803.35 and I am trying to explain it to someone how the annual payment was derived, without doing it in excel. Am I confusing you? Thanks

----- Norman Harker wrote: ----

Hi Kay

Here's how I'd explain "manually" (he says avoiding all sorts o
temptations!

Here's the repayment that I've calculated at 3133.6

Interest is 8%/12 per month or .6666667

After the first month interest is 100000 * 0.6666667% = 666.6

I make a payment of 3133.64 and of that payment 666.67 is interest an
the balance of 2466.97 is repayment of the debt leaving me wit
outstanding debt of 100000 - 2466.97 = 97533.0

After the second month interest is 97533.03 * 0.6666667% = 650.2

I make a payment of 3133.64 and of that payment 650.22 is interest an
the balance of 2483.42 is repayment of the debt leaving me wit
outstanding debt of 97533.03 - 2466.97 = 95,066.0

This goes on for 36 months and at the end of that time I should fin
that the outstanding debt is gone

If you like, I'll send you an amortization table that uses precisel
this approach to show how a loan is paid off. Just apply to the emai
below

--
Regard
Norman Harker MVP (Excel
Sydney, Australi
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments
available free to good homes

thanks for all of the replies. the payment IS annual. but how woul

I show it manually?, say on a calculator or trying to explain it t
someone. I can't just multiply the % by the loan amout because tha
would only give me the interest. Do you see where I'm going

 

[kay]

I got it. I actually had to use an ordinary annuity table but followed the formula for calculating the present value of an annuity. Whew! Thanks for everyone's input!