Chump Bailey Posted January 18, 2007 Share Posted January 18, 2007 Can anyone please explain to me how the figure of $904.55 is arrived at by Excel? What are the operations involved? If you wanted to borrow $20,000 at an 8 percent interest rate and pay the loan back over 24 months, you could use the PMT() function to figure out the monthly payments. In this case, the function would be written =PMT(8%/12, 24, 20000), which calculates a monthly payment of $904.55. Link to comment Share on other sites More sharing options...
drums and skins Posted January 18, 2007 Share Posted January 18, 2007 You're saying you have 24 months... $904.55 x 24 payments = $21,696 Looks about right after interest is considered of the principal amount borrowed ($20,000) Link to comment Share on other sites More sharing options...
Chump Bailey Posted January 18, 2007 Author Share Posted January 18, 2007 You're saying you have 24 months...$904.55 x 24 payments = $21,696 Looks about right after interest is considered of the principal amount borrowed ($20,000) That's true, but I was interested in how the $904.55 was determined - what operations is Excel using to arrive at that figure? Thanks though! Link to comment Share on other sites More sharing options...
Heidenreich Posted January 18, 2007 Share Posted January 18, 2007 Thats neat, I never knew excel could do that. From your formula, the 8% is your interest, it's divided by 12 to get the monthly interest charge. the 24 is the payment terms, and the 20000 is the total amount borrowed. 8% interest on 20000 for the year is 1600, (or 133.33 a month). But that number goes down every month, since the principal goes down. For example, the first payment of $904.55 consists of $133.33 in interest, and 771.22 in principal. The second payment only has $128.19 in interest, since the principal balance is down to 19228.78 Hope this helps.... Link to comment Share on other sites More sharing options...
Chump Bailey Posted January 18, 2007 Author Share Posted January 18, 2007 Getting there but still lost... Take the following example which may be clearer taken from Microsoft's site... Determine monthly payment on a 30 year loan for a home costing $180,000 with an annual interest rate of 6%? Monthly payment = $1,079.19 But how is that determined? Formula would be the following: =PMT(6%/12,30*12,180000) I understand to divide .06/12 months which = 0.005 then what??? Multiply by 360 - the number of total payments then what again with the 180,000 figure - divide? I cannot arrive at the figure of $1,079.19... Link to comment Share on other sites More sharing options...
jrockster21 Posted January 18, 2007 Share Posted January 18, 2007 That's PleaseBlitz/RonJeremy territory right there... Link to comment Share on other sites More sharing options...
SackMachine Posted January 18, 2007 Share Posted January 18, 2007 =PMT(8%/12, 24, 20000), which calculates a monthly payment of $904.55. gave me a monthly payment of -90.45$ Generally in when you are making a loan, the amount is a negative. Should be =PMT(8%/12,24,-20000) No idea how it works Link to comment Share on other sites More sharing options...
BigDaddyShoo Posted January 18, 2007 Share Posted January 18, 2007 The actual formula is: Monthly Payment = P* i * (1+i)^n/((1+i)^n-1) where P = Principle (20,000 in your first example) i = interest per month (0.08/12 in your first example) n = term in months (24 in your first example) Just plug in the numbers and you're golden. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.