Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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.


View Answers     

Similar Excel Tutorials

Do Something Every so Many Rows with a Macro in Excel
How to have a macro do something on a set interval of rows; for instance, input a value every 5 rows. Sections: The ...
Macro to get Data from Another Workbook in Excel
Macro to get data from a workbook, closed or open, over a network or locally on your computer. This is a versatile ...
RATE Function - Calculate an Interest Rate in Excel
The Rate function in Excel (RATE) calculates the interest rate for a financial transaction, such as for an annuity ...
Run a Macro When a Specific Cell Changes in Excel
Run a macro in Excel when a specific cell is changed; this also covers when a cell within a range of cells is chan ...

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
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
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 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
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

Similar Topics









Hi, all. Been a while since I've been here.

Anyway, I'm working on a debt optimizer spreadsheet. I have a version that takes debts and figures out the effective interest rate (taking things like tax considerations, annual fees, etc., into account) for all debts you put in. The idea is you put in a figure, such as $50, that you can put in above and beyond the minimum payments. The spreadsheet applies that payment automatically to the payment with the highest effective interest rate. When one debt is paid off, the minimum payment for that debt and the excess payment are added up and applied to the debt with the next highest effective interest rate, and so on. Thus, your payments from the first month until the last (or more likely, the one before the last) will be the same in total; it's a matter of allocating these appropriately.

I'm including an example of what I mean. C1 has the additional payment. Columns B, G, L, and Q have the interest rates and C, H, M, and R have the payments. Since the highest interest rate here is in the fourth table, the $50 additional payment would be applied to that debt first until it's done. In doing this, the debt goes away at Row 24 with a payment of $220.42. This means that there is an additional $29.58 to be applied to the next highest debt, the .007%. Then, the next month, the full $250 in additional money would be applied to that debt.

My problem is that I keep running into circular references when I try to do formulas. I also have 23 debts (4 mortgages, 4 student loans, and 15 other debts) that the sheet can handle, so the formula needs to be expandable to encompass all of these.

Any help is much appreciated. Ideally, I'd like to be able to enter the formula where the payments already are, but I'm open to additional columns if that's the only way.

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

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.




Hi all,

I need formula or vb macro for this (anything that anyone can provide I appreciate it).

I want to calculate the date of repayment of debt (sales),
the excel sheet is attached for reference and for better understanding what i want.


Thanks in advance.

Regards,
SDCh.

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.

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


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]


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!


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


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.



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


Am looking for template allows for list of all monthly bills,i.e. credit
card,loans etc,and total amount of all combined.attempting to determine if
consolodation is fesabile?



Hello,

I am trying to use the function NPER to calculate the number of payments it would take to eliminate some debt using a few constants.

In the NPER formula, I have the following
RATE = 12%/12 (12% APR, 12 months in a year)
PMT = 60
Pv = 3000
Fv = 0

The results of the formula = -40.7

However, when evaluating it by creating an amortization table, the number of periods requires 70 months to pay the debt off. The way I created the amortization table is by taking:
Debt = A1
Rate = B1
Payment = C1
Cell A2 = A1+A1*B$1$/12-C$1$
Cell A3 = A2+A2*B$1$/12-C$1$
Cell A3 is copied down to A4, A5, A6, A7, and so forth until the last cell has a starting balance of 0 or negative.

Is my amortization table correct, or is NPER populated wrong?

Thanks for any help you can give me.

Alex in Memphis, TN


Hi,

I am looking for a loan amortization template for the following:

a) Shows the repayment for the first few years only for interest
b) Subsequently the repayment will start with both interest and principal.

Therefore, the first few years, the customers only makes repayment for interest and then subsequently makes the repayment like a normal loan payment.Therefore, the loan is packaged in such a way that customer pays a lower repayment amount initially.

Is there any template out there that I could use ?

Thanks in advance and appreciate all the help.




Hi All

I have a particular problem, i took a loan from a family member where they used their credit card, i pay back monthly but occasionally miss a month, the trouble is they use the card for their own purposes as well so its not easy to calculate whats left and how much interest my part has accrued.
Can somebody help me with a calculation where i go back to the start of the loan and enter every time i paid up, and then in real time enter each time i make a payment to run off the debt, i would like to use this also to show the person so he is sure he has been paid correctly.

Thank you in anticipation

Regards

Gordon

hey all..

First time poster..
I feel i'm pretty confident with excel, though i am having this trouble.

I can calculate a repayment no problem, however i'm struggling with an irregular payment..

EG.
$15,000 @ 10% / 60 months = ~318'mo

that parts easy to calculate..
what i want to do is calculate REGULAR payments, if the 5th payment is higher..

EG

4 payments = regular
5th payment - $1000
55 payments = regular

I've tried doing it with am amortisation schedule.. but can't work it out.
any ideas?


I have an excel spread sheet where I have a list of transactions.

Bob paid for bill
Bill paid for bob
James paid for Steve
Steve paid for Bob

I want to create a calculation of whom owes whom money.

I've built the transactions so that that column b is a drop down for who will gain debt and column c will be the person who the debt is owed to.

I now have no idea how to start the list of folks (in column z) to be compaired and totaled.

Thanks for some hints on where to start!