Email:      Pass:    Pass?
Advertisements


Free Excel Forum

Is There A Template For Rapid Debt Repayment In Excel?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Am using Excel 2002. Looking for a template that will calculate time to
repay debts with fixed payments each month, rolling amounts from paid off
debts toward remaining debt. (This type of calculator is available on
cheapskatemonthly.com.) Thanks for any help.



Similar Excel Video Tutorials

Helpful Excel Macros

Create a 12 Month Calendar With The Current Day Highlighted in Excel
- Create a 12 month formatted calendar on a new tab in Excel with the current day highlighted. This macro creates a nicel
Automatically Run a Macro at a Certain Time - i.e. Run a Macro at 4:30PM every day
- Automatically run an Excel macro at a certain time. This allows you to not have to worry about running a macro every da
Format Cells as Time in Excel
- This free Excel macro formats a selection of cells in the Time format in Excel. This Time number format means that inst
Format Cells in The Short Date Number Format in Excel
- This free Excel macro applies the Short Date number format to a selection of cells in Excel. This number format display
Format Cells in The Long Date Number Format in Excel
- This free Excel macro formats a selection of cells in the Long Date number format in Excel. The Long Date number format

Similar Topics







.... I basically have a list of all my debt, with a row defining the priority of the debt.

I have made another sheet, defining my monthly income, and my debts that always NEED to get paid every month (electric, gas, internet, etc)

I want to take the remaining from each month, and start referencing the debt sheet, and taking debts and splitting them into payments for the next year.

So, I could define say, 5 debts i want to pay this year, and split them up into payments for each month --

and i want to go farther with this, perhaps being able to alter it each month, when i get music gear, and then maybe having a running balance of each debt or something.

Any help would be appreciated. Of course, i would like to learn more about excel, so although doing it FOR me would be nice of you, i would rather learn HOW to do it


I need to calculate thousands of values, and the way I need to this is the following:

1) Each value is a debt that was contracted at a specified day, and this may differ from debt to debt
2) The tax is 3% month for every debt
3) I need to know how much these debts were worth at a specified time in the past.

Example: One debt of 104563USD was contracted day 16 november 2008, and I need to know how much it was worth at day 9 july 2009. And the same with many others, but the date the debt took place changes from debt to debt.
I'm doing it manually with HP12c, but it is taking toooo long =/
Any help is appreciated!


Hi. I'm a novice who can create simple spreadsheets. What I need to know is a formula which will allow me to keep a list of outstanding debts, the monthly payout (which changes once one debt is paid off), and keeps a running total for each month. Let me try to explain by example:

Along the left side column would be month and year (May 2007, June 2007, etc.)

The headings above each column might be: van payment, Dr. P, Student Loan #1, Student #2, etc.

The first entry under each column heading would be the outstanding amount of the debt. (i.e., Van = $1342, Dr. P = $219, etc)

Under each of the debt totals would be monthly payments and the new running total. Sometimes, the monthly payments will include more than one per month when a little extra can be added. I would like to be able to show the individual amounts rather than combine them into one.

At the bottom of each column would be the final payment and "0" to show that the debt is paid off and the month it was completed.

Thank you for you help. This involves way too much math for me :o


Hi. I'm a novice who can create simple spreadsheets. What I need to know is a formula which will allow me to keep a list of outstanding debts, the monthly payout (which changes once one debt is paid off), and keeps a running total for each month. Let me try to explain by example:

Along the left side column would be month and year (May 2007, June 2007, etc.)

The headings above each column might be: van payment, Dr. P, Student Loan #1, Student #2, etc.

The first entry under each column heading would be the outstanding amount of the debt. (i.e., Van = $1342, Dr. P = $219, etc)

Under each of the debt totals would be monthly payments and the new running total. Sometimes, the monthly payments will include more than one per month when a little extra can be added. I would like to be able to show the individual amounts rather than combine them into one.

At the bottom of each column would be the final payment and "0" to show that the debt is paid off and the month it was completed.

Thank you for you help. This involves way too much math for me :o


Hi all, hope you guys can help solve a practical accounting problem, and save me a lot of time in the process.


You see, the problem is quit simple, as it is all a matter of checks and balances, as the example illustrates.

Take for instance situation A, were someone has an outstanding debt of 100 on the 1. of january and later on the 2. of january settles the account by making a payment of the exact amount as the outstanding debt.

Now that was the simple situation. Now lets turn to the more realistic situation B, where you have some large outstanding debts, and then a lot of patial payments, such as illustrated in Situation B. In the example there are some combinations of incoming payments that balances the outstanding debt of 200. (eg. both 55 + 45 + 100 = 200 as does the combination 150 + 50). Now the quistion for you is: Is there a way to configure excel to automaticly suggest that both the 3 incomings payments of 55+45+100 equals 200 but at the same time it also suggests that 150 + 50 equals 200?. That is to say ... ask excel to compare all the values in coloum "incoming payment" with one of the amounts in the "outstanding debts" coloum and then find the right combinations?

There are no other data available to cross reference with, other than the amounts. (of course in reality this is not the case)



Ok with the help of another member they were able to come up with this attached simplified worksheet. The basic function is that there are multiple debts and I wanted to add additional payments which would go into the first debt first, then when it reached a zero balance to the second debt and so on.

Its flawless and exactly what I was looking for. But here is my new problem, which I couldn't solve on my own.


I want to add monthly interest and monthly minimum payments to each debt and combine it with the setup I have now where the additional payments subtract that much more from the first given debt and then moves down the line as they reach zero balances.

Any help would be greatly appreciated. Thanks in advance.


Hmmm...I am not quite sure what function is needed. Maybe a COUNTIF or SUMIF?

I am working on a snowball calculator to estimate when a person will be out of debt taking into consideration current balances, monthly payments, interest rates and "snowball" (extra) payments.

With some help, I have got the primary formula working. And it works great as long as you start at the far left (L1, Debt 8) and enter SMALL TO LARGE working towards (S1, Debt 1).

However, if you enter the debts out of order, or even if you enter them in order but have with a large payment that pays off ahead off the debts to your "left" then the sheet doesn't calculate properly. Look at columns O, P & Q (Debts 5, 4 & 3 respectively) to see what happens.

The reason is because the formula is designed to use data only 1 column to the left. For instance P14 is calculating based heavily on conditions in O14, but without regard to Q14 or even N14 for that fact.

So to fix it, the formula needs re-written for a condition based on a RANGE. And I'm just not familiar with doing this.

The obvious "easy" answer is to have a user enter their debts appropriately and if they encounter this glitch, then to re-arrange until it calculates properly. Unfortunately, not everyone is a geek and will catch the glitch.

So how does one go about defining the code properly?

I am attaching a screen shot, as well as the source file so you can closely examine all elements. And here is the primary formula providing the current functionality.

Quote:

=IF(P130,IF(P13

As an example, I have a debt of $10,000 which incurs interest at 7% per annum and I intend to make annual repayments of $3,000.

How do I write an Excel formula that will tell me the time it will take me to repay the debt?

Thanks


Hi i have problem with calculating debt repayment if debt is drawn in multipal years. i am looking for a solutition which gives me results as after total drawdown based on Moratorium period the repaymnet should start of Respective debt draw down.

Sample sheet is attached

Please help


I'm creating a stacked column graph to create a debt maturity schedule and each column consists of two variables, fixed rate and variable debt, and I'd like the data labels to show the sum of the fixed and variable debts, instead of having two data labels per column showing the values of the variable and fixed rate debt seperately.


I have several debt amounts of varying numbers which carry over from month to month (as all debt does).

For Each month I have dedicated a cell for additional payments.

Now heres where I reach my problem... I will simplify it a lot...

I want the debt to be paid off in a particular order (credit -> car -> student loan). SO, if I only owe 100$ on the credit card and i put in 500$ in the additional payment cell (for that particular month), I want it to know that the credit card gets paid first up to 0$ and the rest gets placed into the car debt. and if perchance the credit card is at zero and then the car loan zeros out I want the rest to be placed in the student loan.

I want to do this with just one (additional payment) cell per month, rather than a payment cell each month for each debt.

I hope i wasnt too confusing.

thanks


I have several debt amounts of varying numbers which carry over from month to month (as all debt does).

For Each month I have dedicated a cell for additional payments.

Now heres where I reach my problem... I will simplify it a lot...

I want the debt to be paid off in a particular order (credit -> car -> student loan). SO, if I only owe 100$ on the credit card and i put in 500$ in the additional payment cell (for that particular month), I want it to know that the credit card gets paid first up to 0$ and the rest gets placed into the car debt. and if perchance the credit card is at zero and then the car loan zeros out I want the rest to be placed in the student loan.

I want to do this with just one (additional payment) cell per month, rather than a payment cell each month for each debt.

I hope i wasnt too confusing.

thanks


Hi all, this is going to be a good one
I have a workbook that shows Total Balance then a brake down in ranged aged debt by the days (Aged debt report) I also use it as a debt collections tool

Total Balance, D0-29, D30-59, D60-89, D90-119, 120-149 & D150+


I have 1500 customer records and I want to be able to insert a extra column (amount paid) and when I enter a payment amount it looks at the old debt first subtracts oldest debt first

Example: Total Bal is $500, D0-29 is $250, D30-59 is $100, D60-90 is $150
If a customer was to pay $200 I want be able enter that amount in the paid cell and in return the aged debt buckets(cells) update automatically and reduce by age....

now it would look like:
Total Bal is $300, D0-29 is $250, D30-59 is $50, D60-89 is $0

PS: yes I know your thinking that dumbs. It should be based on invoice not aged debt but thats the world I live in.

thanks heaps in advance Rusty


My company uses the aging method of determining bad debt expense. I need to calculate the appropriate balance in the allowance for bad debts account. Based on historical analysis: amounts less than 16 days outstanding have a 98% chance of being collected; between 1 and 30 days probability is 90%; 31-45 days=85%; 46-60=75%; 61-75=40%; and over 75 days outstanding, there is no chance of collection.

I need some more calculations:
The spreadsheet will have the following features:
1.Calculates the appropriate balance in the allowance account
2.Allows the user to enter the dollar amount of accounts outstanding in each category. These amounts are available from another computer report, the aged trial balance.
3.Is easy to follow and understand
4.Does not allow unauthorized changes to likelihood of collection percentages[/b]


My company uses the aging method of determining bad debt expense. I need to calculate the appropriate balance in the allowance for bad debts account. Based on historical analysis: amounts less than 16 days outstanding have a 98% chance of being collected; between 1 and 30 days probability is 90%; 31-45 days=85%; 46-60=75%; 61-75=40%; and over 75 days outstanding, there is no chance of collection.

Please help me build a spreadsheet that will have the following features:
1.Calculates the appropriate balance in the allowance account
2.Allows the user to enter the dollar amount of accounts outstanding in each category. These amounts are available from another computer report, the aged trial balance.
3.Is easy to follow and understand
4.Does not allow unauthorized changes to likelihood of collection percentages


I really need some help as i am struggling to come up with a solution for this.

We have a debtors spreadsheet that looks at those debts over 90 days and then we manually categorise them into what stage they are at eg 14 day legal letter, With Solicitors, Write off, Payment Schedule etc.

We are looking to automate the process slightly. If something is with the solicitors it tends to stay this way for a few months, so we know that if it was in the solicitors column in July it will still be in the solicitors column in Aug. I've been using the following formula to pick up this data

=IF(ISBLANK('July'!G6),0,$C5)

where G6 is the cell for 14 day letter sent and C5 contains August's total.

The problem that i am getting is that the rows don't always match for example we may have one Debtor that has paid off their Debt or we have a new Debt that has just rolled into 90 days. Ideally i need a formula that will give me the same detail as above (providing a 0 if that column in the previous month was blank and providing the total for the new month if it wasn't), but i need it to be based on looking up the same Customer number in both sheets.

Is there anyway that i can do this?

Any help would be greatly appreciated.


Hi all!

I have a loan from my father which I need some help calculating. Hopefully, some of the experts in here can give me a hand

What I'm looking for is something which will let me enter the day the money was lent (there are several smaller amounts), and calculate these including interest and interest on the interest. Then, I will pay back in several steps, this debt but I would like to subtract the paid amount on the day it's registered and then stop acumulating interest. The last step is to show the outstanding debt.

I guess the payments and the calculation of interest could be done by some sort of nested, re-iterating function...?...

If someone could help me with this I would be REALLY grateful :D

Cheers!


Howdy!

I'm trying to figure out how to do an equation in EXCEL - hopefully someone here is good at maths!

This is a financial equation, so the top number represents value and the bottom debt.

Required Percentage = 33%

Value = 100
Debt = 40

Doing the equation... 40/100 = 40%

What I want is for EXCEL to calculate the required "Input" which reduces the debt /value ratio to 33% (adjustable to whatever is required)

Doing it manually...

Input = 10
Value = 90 (100-10)
Debt = 30 (40-10)

30/90 = 33%

So it this instance, I would enter in 33% into a field, EXCEL would look at the current Value and Debt values and calculate the answer is 10.

Anyone?

Regan


I want to set up a spreadsheet where I can track my debt reduction. I want
to be able to put in the balance owed, the interest rate, payment amount
(that can be changed). I want to be able to have all my debt on one
spreadsheet and show progress on each individual debt as well as on the debt
as a whole. I have seen software you can buy that does this but I am sure
that you can do it in Excel somehow. Excel 2000 or later.



There are 3 equations I am working on. 1 is my Net cash, this looks at a bunch of data then says how much money I have currently. It then should look and say, if I don't have at least 30,000 in net cash I need to borrow enough money to get to 30,000 (Add: Borrowing from bank). Then my next row is the "Less: Repayment to the bank", here the formula should say if I have more then 30,000 in net cash, AND I owe the bank money from previously borrowing to bring my net cash to the minimum of 30,000, then I need to pay as much of my debt as possible without going under the minimum needed value of 30,000. The problem I am running into logically at least is if I owe 10,000 and I currently have 37,000 how does my formula deduct that 7 from my net cash on hand as well as deduct it from my 10,000 owed so that my end of year cash shows as 30,000 and my end of year debt is 3,000? I attached the snippit of the spreadsheet below using excel jeanie, let me know if I did it incorrectly.

In the snippit it does not show any cells regarding previously owed debt, but the equation would need to take any existing debt into account.

Thanks guys.

Sheet1

A B C 54 Net Cash Position before borrowings and repayment of debt NA $39,677 55 Add: Borrowing from bank NA $0 56 Less: Repayment to bank NA $0 57 Equals: End-of-year cash on hand $30,000 $39,677
Spreadsheet Formulas Cell Formula C54 =B57+C52 C55 =IF(C54 C10, B63 > 0) ,C54-C10,0) C57 =C54+C55-C56

Sheet1

B 67 Minimum Cash 68 $ 30,000.00

Excel tables to the web >> Excel Jeanie HTML 4


hi every one can any body tell me an equation that will calculate the # of years that I will need to pay my Debt.
Ex. my debt is 200,000 with an annual Interst of 6%, and I can only pay 13,500 a year how many years would take to pay off my debt.
thank you


Template that calculates and schedules both interest and capital elements of
monthly repayments for a loan. eg. £10000 borrowed at 10% apr over 36
months repaid by monthly instalments. Require for each month the repayment,
capital, interest and remaining capital outstanding value. Ideally this
could also show capital to be repaid within next 12 payments and capital to
be repaid after more than 12 months on a rolling basis.



Hi there. Please see the simple table below with 4 columns of data. Unfortunately I cannot paste the detail in so the format looks a bit squashed :-


US$ DEBT Result Days
5,764 BAD DEBT " " 180
4,452 BAD DEBT 4452.05 1,368
-8,416 BAD DEBT FALSE -91





My formula is in column "C" and is as follows


=IF(B2="BAD DEBT",IF(AND(D2>1,D2

Greetings!

Looking for help in building a spreadsheet to support a bond issue. The components are as follows:


Date of Bond (advance date ) June 1, 2008
Amount: $451,689
Rate: 8%
Maturity: 20 year
Repayment: semi annual beginning December 1, 2008
Source of repayment is tax revenue of which we would plan on receiving:

6/1/09 & 12/1/09 $3,379
6/1/10 & 12/1/10 $10,137
6/1/11 & 12/1/11 $16,895
6/1/12 & 12/1/12 $23,653
6/1/13 & 12/1/13 $30,411
June 1st and Dec 1st 2014 through 2027 $33,790

The trick is that interest will be capitalized until such time the scheduled repayment (tax increment payment) will service debt so the bond will likely peak at say around $573,225. I am guessing I will need to put an assumption date in the spread sheet where the user can input the date they think it will be able to service the debt.


Hi,
I am making a calculator to back into a pre-defined debt to income ratio to refinance. I just can't figure out how to do this so any help would be appreciated.

I need to take the sume of d35 and d46 and subtract the amount needed to reach a pre-defined debt to income ratio (which I currently input into cell b53). The resulting reduction in expenses needs to populate in call d45.

I pasted a screenshot below to show what I need.

Thanks for all the help everyone. I know it's probably simple algebra.....but I have been out of school way to long.

Loan Modification Calculator

  A B C D 29 MONTHLY EXPENSES     HomeOwner 30 1. Installment payments ( min Pmt )     $600.00 31 2. Revolving payments ( min Pmt )     $0.00 32 3. Car payment(s)     $600.00 33 4. Existing Other Homes PITI     $0.00 34 5. Other     $0.00 35   Total Expenses $1,200.00 36         37         38 CURRENT HOUSING EXPENSES     HomeOwner 39 1. Fully Amortized Pmt Principal Home     $3,000.00 40 2. Mortgage Insurance     $0.00 41 3. Property Taxes     $5,000.00 42 4. Hazard and Flood Insurance     $400.00 43 5. HOA/Condo  Fees     $100.00 44 6. Other     $0.00 45 Amount you need to Reduce your Payment to Qual.       46   Total Expenses $8,500.00
Spreadsheet Formulas Cell Formula D35 =SUM(D30:D34) D46 =SUM(D39:D45)

Excel tables to the web >> Excel Jeanie HTML 4