Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
credit calculations
Hi folks,
I am wondering how I can calculate the interest paid on a credit card and how long it will take me to pay off my debt if I make minimum payments of 5%. Many thanks, Danny |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
credit calculations
Look at the NPER() function.
Make sure you define correctly what you mean by "5%". 5% of what? Initial debt? Outstanding amount? Also look at the template and instructions that can be downloaded he http://office.microsoft.com/en-us/as...346401033.aspx -- Kind regards, Niek Otten "msnews.microsoft.com" wrote in message ... Hi folks, I am wondering how I can calculate the interest paid on a credit card and how long it will take me to pay off my debt if I make minimum payments of 5%. Many thanks, Danny |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
credit calculations
Dear Niek Otten,
Thank you for your help. The problem with NPER is that it looks like it requires constant payments. whereas I am looking at 5% of the outstanding balance. e.g. I borrow £1000 on a credit card on 1.2% monthly interest. I only make monthly payments of 5% of the outstanding balance. How long will it take me to clear the debt and what will the interest be? Can I use NPER? Danny Thank you for "Niek Otten" wrote in message ... Look at the NPER() function. Make sure you define correctly what you mean by "5%". 5% of what? Initial debt? Outstanding amount? Also look at the template and instructions that can be downloaded he http://office.microsoft.com/en-us/as...346401033.aspx -- Kind regards, Niek Otten "msnews.microsoft.com" wrote in message ... Hi folks, I am wondering how I can calculate the interest paid on a credit card and how long it will take me to pay off my debt if I make minimum payments of 5%. Many thanks, Danny |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
credit calculations
I don't think any built in function will do this, Norman Harker wrote a
functions for this First in the workbook where you do this press Alt + F11, click insert module and paste in Function CardNPER(Loan As Double, LoanRate As Single, RepayPercent As Double, RepayMinAmount As Single) Dim SuckerRepayment As Double Dim Interest As Double Dim NeverNever As Integer Do Until Loan = 0 If RepayPercent * -Loan = RepayMinAmount Then SuckerRepayment = RepayPercent * -Loan Else If RepayPercent * -Loan -Loan Then SuckerRepayment = -Loan + (RepayPercent * -Loan) Else SuckerRepayment = RepayMinAmount End If End If Interest = Loan * LoanRate Loan = Loan + SuckerRepayment + Interest NeverNever = NeverNever + 1 Loop CardNPER = NeverNever End Function Make sure you don't get any line wrap press alt + Q to close the VBE then use it as in =CardNPER(-1000,1.2%,5%,10) where -1000 is the starting balance, 1.2% the rate, 5% the payment and 10 is the minimum value you need to pay per month (10 pounds?) According to that it would take 65 months with 10 as minimum value but with 1 as a minimum vale it would take 124 months If you really find this useful you can open a new workbook, repeat the paste into VBE, save the workbook as an add-in (*.xla), then check it under toolsadd-ins and restart excel and it will always be available for any workbook It obviously is not a good way to pay off a loan thus parts of Norman's code like Dim SuckerRepayment As Double -- Regards, Peo Sjoblom (No private emails please) "Danny J" wrote in message ... Dear Niek Otten, Thank you for your help. The problem with NPER is that it looks like it requires constant payments. whereas I am looking at 5% of the outstanding balance. e.g. I borrow £1000 on a credit card on 1.2% monthly interest. I only make monthly payments of 5% of the outstanding balance. How long will it take me to clear the debt and what will the interest be? Can I use NPER? Danny Thank you for "Niek Otten" wrote in message ... Look at the NPER() function. Make sure you define correctly what you mean by "5%". 5% of what? Initial debt? Outstanding amount? Also look at the template and instructions that can be downloaded he http://office.microsoft.com/en-us/as...346401033.aspx -- Kind regards, Niek Otten "msnews.microsoft.com" wrote in message ... Hi folks, I am wondering how I can calculate the interest paid on a credit card and how long it will take me to pay off my debt if I make minimum payments of 5%. Many thanks, Danny |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
credit calculations
"Danny J" wrote:
e.g. I borrow £1000 on a credit card on 1.2% monthly interest. I only make monthly payments of 5% of the outstanding balance. How long will it take me to clear the debt and what will the interest be? Longer than would ever be reasonable, based only on those rules. As a practical matter, you should also stipulate a minimum payment. Spreadsheets (Visicalc) were invented, in part, to provide answers to such "what-if" questions not with plethora of built-in functions, but by allowing you to model the situation. Models often give you greater insight into what is happening with the numbers, allowing you to hone your strategy more effectively. I suggest that you contruct the following spreadsheet: A2: payment number: 1,... B2: payment date: 2/1/2006, 3/1/2006,... C2: interest: =F1*1.2% D2: payment: =ROUND((F1+C2)*5%, 2) E2: additional payment (blank for now) F2: balance: =F1+C2-D2-E2 A1 = 0 C1: total interest: =SUM(C2:C250) F1 = initial balance (1000) Highlight A2:F2 and drag the lower-right corner down for as many rows as needed until the balance is reduced to nearly zero. You should be able to see the problem. Notes: 1. When we drag the row that way, Excel increments B2 (date) by one. There are several ways to work around the "problem". If they are not obvious to you, repost here. 2. The formula for C2 is valid at least for C3 and beyond. It might be valid for C2, as well; alternatively, it might be zero. It depends on your repayment agreement. 3. I purposely do not round interest and balance, whereas payment must be rounded. If you choose to round interest, I would suggest using ROUNDUP(), a worst-case assumption, unless your lender tells how they calculate interest (unlikely!). |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
credit calculations
Thanks Guys!
" wrote in message ... "Danny J" wrote: e.g. I borrow £1000 on a credit card on 1.2% monthly interest. I only make monthly payments of 5% of the outstanding balance. How long will it take me to clear the debt and what will the interest be? Longer than would ever be reasonable, based only on those rules. As a practical matter, you should also stipulate a minimum payment. Spreadsheets (Visicalc) were invented, in part, to provide answers to such "what-if" questions not with plethora of built-in functions, but by allowing you to model the situation. Models often give you greater insight into what is happening with the numbers, allowing you to hone your strategy more effectively. I suggest that you contruct the following spreadsheet: A2: payment number: 1,... B2: payment date: 2/1/2006, 3/1/2006,... C2: interest: =F1*1.2% D2: payment: =ROUND((F1+C2)*5%, 2) E2: additional payment (blank for now) F2: balance: =F1+C2-D2-E2 A1 = 0 C1: total interest: =SUM(C2:C250) F1 = initial balance (1000) Highlight A2:F2 and drag the lower-right corner down for as many rows as needed until the balance is reduced to nearly zero. You should be able to see the problem. Notes: 1. When we drag the row that way, Excel increments B2 (date) by one. There are several ways to work around the "problem". If they are not obvious to you, repost here. 2. The formula for C2 is valid at least for C3 and beyond. It might be valid for C2, as well; alternatively, it might be zero. It depends on your repayment agreement. 3. I purposely do not round interest and balance, whereas payment must be rounded. If you choose to round interest, I would suggest using ROUNDUP(), a worst-case assumption, unless your lender tells how they calculate interest (unlikely!). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculations based on PivotTable information | Excel Discussion (Misc queries) | |||
looping through a set of calculations | Excel Worksheet Functions | |||
Calculations crossing multiple sheets | Excel Discussion (Misc queries) | |||
time interval calculations in excel | Excel Discussion (Misc queries) | |||
Invoice and Credit Notes | Excel Worksheet Functions |