Hello,
In excel I would like to try and work out how long it will take me in weeks & years to pay off a loan, and also be able to change the value of the weekly and or monthly payments and then have the weeks and years to pay off the loan reflect that change...
I have been trying to do it using the pmt function and looking online, but nothing seems to be standing out.
Any help would be greatly appreciated.
Solver could answer it
but, (without some example) you basically need 6 things:
Loan Amount
Down payment
Loan Borrowing Rate (ie- Annual % Rate or 'APR')
Accrual Rate (ie-daily/weekly/bi-weekly/monthly/yearly (this affects outstanding balance at any given time))
Length of Loan (Usually in months)
Payment terms (ie- weekly/bi-weekly/monthly...etc......)
once you have those, you can calculate your payments, generally, no matter what the accrual rate is, the interest expected is calculated per annum (yearly), then divided by the number of payments for a given year, if you miss one, that amount times the missed payment (and possibly a penalty) is added to your bill
So..
Calculate your payment schedule (ie 5years=60months,monthly payments,no down payment..just to make it easier)
then,using the APR, you can calculate monthly payments to figure out your payment is (and Excel has some built-in functions (look under the financial formulas (PMT ...which is based on a constant interest, but should help)) to help with this, but you should understand at least the basics of what you are trying to do ))
Hope this helps
EDIT-
This is also why most people get into credit card problems, the minimum payment on the bill, basically covers that months outstanding balance's interest, but the interest on any previous months outstanding balnce is added into the whole outstanding balance and calculated again...loan sharks are at least "honest" about it...lol
Last edited by dredwolf; 03-14-2014 at 05:06 AM.
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
HI Dredwolf.. that did help a bit, thank you.
But I will give an example, just say I have already borrowed $1,000 from the bank as a "personal loan" and I know the interest rate is 10%. They have already told me that I need to make weekly payments of $10 to pay the loan back over a period of 10 years (the figures are obviously fictitious).
What I would like to be able to do is put those figures into a spread sheet, so I can then adjust the "weeks to pay off"
So based on the above figures it would take me 10 years X 52 weeks per year - 520 weeks
Well I would like to be able to adjust the 520 weeks cell, and then the weekly loan payment column would adjust itself accordingly.
So if i decided to change it to 260 weeks, the repayment amount would change to roughly $20.. or if I changed the repayment amount from 10 to 30 then the weeks to pay off would change to a 3rd of 520.. roughly.
basically, as long as your not too concerned about precision and interest changes due to changed times,
base#weeks*base$payment/new#weeks=new$payment, and
base#weeks*base$payment/new$payment=new#weeks
asI said this does not include changes to interest due to shortened or lengthened time periods, but as a (VERY) rough calculation, it seems to be what you want ?
Hi Dredwolf,
I sort of understand what you are saying.. but not sure how to use what you have written in a formula.
To go into more detail If I had the below in columns A1 through to A8
Loan Start Date
Loan End date
Outstanding Loan Amount
Interest Rate
Weekly Payment
Yearly Interest Rate
Proffered Pay Out Date
Total Weeks to Pay Out
And then I guess I would use Columns B1 through B8 to enter the values, I would like the below scenarios to be able to take place..
Or maybe I would need to layout the values and headings better.... but hopefully you get what I mean.
So what I would like to do is if I enter in values for Outstanding Loan amount, and Yearly Interest Rate, and then enter a value in "Total Weeks to Pay Out" all the other values will populate.SO then I know how much I would have to pay per week if I wanted to pay the loan out in a given amount of weeks.
Also, If I just fill in the Loan End Date, Outstanding Loan Amount, and Interest Rate, It will calculate how much I have to pay per week to pay the loan out by the end date.
Or, If I enter in the Outstanding Loan amount, Yearly interest rate, and Weekly Payment amounts,
I will then be shown total weeks to pay of the loan, and the Loan End Date.
So then If I think OK, I can pay off 100 bucks it will tell me the loan will be paid out in say 20 weeks, and the date in 20 weeks time. And then I change the Weekly Pay off amount from 100 to 200 and hit "calculate" it would show me 10 weeks to pay out and the date in 20 weeks.
Would I need some sort of sheet that as the various calculations already in place, and then depending on if there where values in specific cells it would then know what calculation to use?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks