PMT Function

Stephanie Layne

New Member
Joined
Jun 6, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. MacOS
How to use PMT function to figuring out periodic loan payment as a positive number?
Given Info:
Negotiated Cost of Vehicle $35,000.00
Additional Down Payment $5,000.00
Sales Tax Rate 5%
Term of Loan in Years 5
# of Payments Per Year. 12
APR Based on Credit Rating 3.25%
Total Down Payment $6,750.00
Amount of the Loan $28,250.00
I've been stuck trying to figure the question for an assignment for a while now. If someone could please advise me on what to do it would appreciated.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

joeu2004

Banned user
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows
I am very familiar with loan calculation, but I do not understand some of the terms that you include. I suspect they are added to throw you off; that is, irrelevant information to confuse you.

All that is financed is the "amount of the loan"; that is, $28,250.

The "negotiated cost", the "additional (sic) down payment", the sales tax, and the "total (sic) down payment" are irrelevant for the purpose of calculating the monthly payment.

Arguably, they might be relevant for the purpose of calculating the true APR.

But if the stated "APR" is not, in fact, the annual interest rate, you have not provided sufficient information to calculate the monthly payment.

(In US, the terms "APR" and "annual interest rate" are potentially different, even though "Annual Percentage Rate" sounds the same.)

Since this is an "assignment", I do not want to spoon-feed the solution to you. Instead, I'll pass along some tips that should help.

If you want to calculate a "periodic" payment (monthly, since there are 12 payments per year), you must convert the annual interest rate (presumably 3.25%) to a periodic rate. Typically (in the US), we do that by dividing the annual rate by the number of payments per year. Some other countries does it differently for some loans (mortgages).

Likewise, the loan term in years must be converted to the number of payments for the term of the loan. That is, multiply by the number of payments per year.

Finally, if you enter the loan amount as a positive number, PMT returns a negative number. If you want a positive result, simply negate the PMT result. In other words, =-PMT(...) instead of =PMT(...).

Alternatively, you can enter the loan amount as a negative number. In other words, -28250 instead 282500, or -B8 instead of B8.

The key is: Excel uses "signed cash flows" for most financial functions. (But not all, sadly.) That means: assign one sign (plus or minus) to inflows, and assign the opposite sign (minus or plus) to outflows.

It does not matter what you call an inflow or outflow; it depends on your point of view (lender v. borrower). It does not matter which sign you use for inflows; just be consistent.

I choose my signs so that functions return positive values. So, in this case, I would use -28250 (inflow for the borrower; outflow for the lender) so that the PMT result is positive (outflow for the borrower).
 

joeu2004

Banned user
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows
You're welcome. If you want further help, show us your formula, as well as any cell values that are referenced.

I have no problem correcting mistakes, offering explanations, etc, even for an assignment. I just balk when the student has nothing to offer.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,825
Messages
5,766,666
Members
425,367
Latest member
Boboka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top