PDA

View Full Version : Monthly Payments With a Final Payment



Bob Phillips
06-28-2018, 10:16 AM
I am struggling to get a formula that matches a quote I have been given. The quote is for a simple purchase, with initial and final payments.

I had assumed PMT(rate,nper,pv,fv) would do it, but it does not match for me.

The numbers I am working with are:
rate - 3.9% APR
nper - 36 months
pv - 16,950, price of 21,950 with a deposit of 5,000
fv - 8,933.50

They come up with 36 monthly payments of 235.91. For the life of me I cannot get to this number.

Anyone solve it for me?

Paul_Hossler
06-28-2018, 01:44 PM
There's never an accountant around when you need one

I can get close -- Not sure if it's correct

I couldn't find any thing in Google about calculating monthly payments with a balloon payment at the end


22494


Edit:

According to this site, the payment would be higher, but the formula seems more accurate and STILL gives the same answer PMT() does

22495

Logit
06-28-2018, 09:08 PM
.
Example with free download : https://www.vertex42.com/Files/download2/themed.php?file=balloon-loan-calculator.xlsx

https://templates.office.com/en-us/Balloon-loan-payment-calculator-TM03986854

http://exceltemplate.net/finance/balloon-loan-payment-calculator/

https://www.free-power-point-templates.com/articles/free-balloon-payment-excel-template/

http://www.tvmcalcs.com/index.php/calculators/apps/excel_loan_amortization_with_extra_payments

Bob Phillips
06-29-2018, 02:29 AM
I can get close -- Not sure if it's correct


The problem I have with that Paul is that it assumes that the final payment is not part of the loan, it is taken out of the present value. This seems totally wrong to me, the finance company is paying that amount out and does not get it back until the end and so is loaning that amount for the whole term. But it does get very close to what I am being quoted!

Paul_Hossler
06-29-2018, 08:05 AM
I didn't like it either -- just didn't 'feel' right, even if numbers were close


I tried 3 different ways

1. Excel PMT() formula ' =PMT(Rate,Periods,AmtFinanced,Balloon,0)

2. Calculate PV of balloon, and subtracting that from amount financed

3. Financial formula web site


Got same answer each way (probably the same one you got)


22497


Maybe they decided you were nice guy and gave you a break?