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?
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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
Capture.JPG
Edit:
According to this site, the payment would be higher, but the formula seems more accurate and STILL gives the same answer PMT() does
Capture.JPG
Last edited by Paul_Hossler; 06-28-2018 at 02:42 PM.
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
.
Example with free download : https://www.vertex42.com/Files/downl...alculator.xlsx
https://templates.office.com/en-us/B...tor-TM03986854
http://exceltemplate.net/finance/bal...nt-calculator/
https://www.free-power-point-templat...xcel-template/
http://www.tvmcalcs.com/index.php/ca...extra_payments
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!Originally Posted by Paul_Hossler
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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)
Capture.JPG
Maybe they decided you were nice guy and gave you a break?
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Posting Permissions
Reply With Quote
