[dstebbins]

I'm trying to create a budget for a small business. I have to factor in the loan payments, but in order to figure how much they'll be, I'll need to know how much I'm going to be borrowing.

Various banks have calculators on their websites that tell you these things, but as I put in the loan payments, my costs fluctuate dramatically. This requires that I modify the amount to borrow for my start up funds, and I end up having to repeat this process over and over again.

Specifically, what equation do they use to factor my minimum loan payments? This way, I don't have to keep going back to this website calculator; I can do it myself.

[BigMoosie]

dstebbins said:

I'm trying to create a budget for a small business. I have to factor in the loan payments, but in order to figure how much they'll be, I'll need to know how much I'm going to be borrowing.

Specifically, what equation do they use to factor my minimum loan payments? This way, I don't have to keep going back to this website calculator; I can do it myself.

So you are taking on a loan, do you plan to calculate exactly enough to only pay off the interest or do you want to reduce the loan by paying off the principle over a certain number of years?

Also, so I can make my answer sound more relevant to you, please provide some ballpark figures (loan amound, annual interest + compounding period, time to repay if relevant and time between installments) so I can make my answer relevant to you instead of providing complicated algebra with terms you might not even need.

[the tree]

The thing is, assuming compound interest, the total amount that you're going to pay back depends on how long your going to spend paying it back.

As an example -ish. For a loan of units, at an annual rate of , paid back over years. Your looking at annual payments of about:



But that carries a lot of assumptions with it - it all really depends on the banks terms.

[dstebbins]

BigMoosie said:

Also, so I can make my answer sound more relevant to you, please provide some ballpark figures (loan amound, annual interest + compounding period, time to repay if relevant and time between installments) so I can make my answer relevant to you instead of providing complicated algebra with terms you might not even need.

Well, I don't know exactly how much I'm going to be taking out, because factoring in the loan payments will alter my start up costs.

Let's just say I take out a loan, and have a principal and origination fee totaling P dollars (the amount I'll be in debt immediately after putting pen to paper). The interest rate is R, and it compounds F times per annum. I want to pay off the principal and interest, to the point where I am entirely free and clear, in Y years, making M payments per year (I say M because I'll probably be making monthly payments).

Are there any other variables you need to know? Thank you.

[BigMoosie]

Ok, I will assume that when the bank does its compounding, it will use the largest $ value that was in debt during that period for its interest calculation, that is how most banks work.

Given your parameters,

P = Principal
R = Annual interest rate (eg. 10% = 0.1)
F = Compounding periods per year
Y = Years until debt is cleared
M = M payments per year

Find the effective interest per compounding period:



Then your regular installment will be:



This can easily be rearranged to make the Principal be the subject of the equation if you want to work out how much you should borrow given a certain installment.
You can easily test the validity of your result with a scientific calculator:

1. Enter the principal and press "="
2. Enter the following with the variables replaced with your values: "Ans*i - X*(F/M)"
3. Continually press "=" the number of times that you will make installments, i.e. M*Y times
4. Your calculator should now show 0 (might be slightly off due to rounding errors)

If the timescale for paying off this debt is very long, such as a decade or more, then you may consider recalcuating your installments to increase each year in line with inflation, this will allow you to make smaller installments for the early years.

This post has been edited by BigMoosie: 18 March 2009 - 10:33 AM

[dstebbins]

So is X the amount of my payment?

Also, what's with the Y.F? Do you mean "Y multiplied by F?"

This post has been edited by dstebbins: 18 March 2009 - 08:49 AM

[BigMoosie]

Yes, X is the amount of the payment.

Yes, Y.F means "Y multiplied" by F.

There's one extra thing, I assumed that your installments are more frequent or equal to the compounding rate, if it compounds more frequently than you plan to install (such as daily compounding) then you will need to make an adjustment:

Let:



and drop the term in the calculation of X.

Note: I made an error in my previous post which I just corrected.

[dstebbins]

Okay, new problem.

The whole reason I wanted this formula in the first place was because I'm writing a spreadsheet (open office calc) for my budget. I wanted to put the formula for the loan in a seperate sheet in the book, and include the payments in my recurring costs (as opposed to permanent costs). The total recurring costs (including other equipment and stuff) for a whole quarter of a year, combined with the permanent costs, would give me my start-up costs, which would be the principal of the loan I take out.

I wanted arrange all three of these cells (loan payment, start-up costs, and loan principal) to affect each other in a triangle-like relationship, so it would factor in the minimum amount I'd need to borrow for the rest of the costs, without me having to manually put in new payments, and then adjust the start-up costs accordingly.

Understand? Didn't think so.

Anyway, when I do this, I get a message in the cells that says Err:522. I'm pretty sure I know what that means; the question is: How do I circumvent it? I couldn't find anything in the help section about this.

[BigMoosie]

If you are using a spreadsheet you don't even need this formula, just prepare your columns for outstanding principal, etc. and use "goal seek" to adjust your principal to make the outstanding principal be 0 at some designated time. If you don't know what "goal seek" is I think you may very well find that it solves your problems.

I don't know "Err:522" means.

[dstebbins]

I've tried goal seek, but it says I need a formula.

Here's a screenshot to show you exactly what I'm talking about.

http://s270.photobuc...spreadsheet.jpg

[BigMoosie]

I'm sorry, but I'm here to help with math problems, not software ones. Try searching the net for "spreadsheet loan payment", your problem is not unique at all.

If you're still spreadsheet illiterate perhaps someone else will help you.

[infomercialscam]

Hello....
Its really a very good thing that the bank is provided the loans against the Education and also with a low interests..
If some people is not rich or not have a money to study then in this problem the bank will help those persons with providing the loan for the education on 10 or 11 % interest.. Its a very helpful for all.

[dstebbins]

infomercialscam said:

Hello....
Its really a very good thing that the bank is provided the loans against the Education and also with a low interests..
If some people is not rich or not have a money to study then in this problem the bank will help those persons with providing the loan for the education on 10 or 11 % interest.. Its a very helpful for all.

Wtf?

1) Can you try repeating that? In ENGLISH, perhaps?
2) What does all of that have to do with my (no solved) issue?

[jackson33]

dstebbins said:

I'm trying to create a budget for a small business. I have to factor in the loan payments, but in order to figure how much they'll be, I'll need to know how much I'm going to be borrowing.

Various banks have calculators on their websites that tell you these things, but as I put in the loan payments, my costs fluctuate dramatically. This requires that I modify the amount to borrow for my start up funds, and I end up having to repeat this process over and over again.

Specifically, what equation do they use to factor my minimum loan payments? This way, I don't have to keep going back to this website calculator; I can do it myself.

Think your best bet would be to use "those" calculators. Feel your trying to establish a figure based on several loan scenarios or trying to establish what combination of interest/principle, comfortably fits your business plan. Things may be different today, but I used to simply budget a figure, go to the bank I intended to use and give that figure. He/she, would give me a couple different plans (Insurance/Taxes or Principle included). One important thing you NEED to remember, "Pre Payment Clauses" are NOT always included in loans. Make SURE you have one....

===============================
Simple Amortization Chart
Download a Spreadsheet for Creating a Simple Amortization Chart in Excel
An amortization chart is created from an amortization table or amortization schedule to show visually how the balance, cumulative interest, and principal change over time. Amortization charts are also very useful for comparing two different loans. The purpose of this page is to highlight two tricks for creating these charts, and provide you with a free simple amortization chart template. You may also want to check out our articles on Simple Interest or download our Simple Interest Loan Calculator.
http://www.vertex42....ortization.html


Create your own chart... http://www.myamortizationchart.com/

[psychlone]

[quote name='psychlone']No. The above eqaution from Tree's post is correct and contains all the variables you well require to build you speadsheet. [/QUOTE]

To recap on earlier post.


where is equal to monthly repayments. Take these numbers for example.

$100,000.00 Principle.
@ 0.00604166% monthly interest (7.25% year).
over 300 months (25 years).
Yeilds.
$722.80 Monthly repayments.
** ONLY AN EXAMPLE**

This equation can calculate the entire amount of interest incurred of the full life of the loan (Instantly). What you must do is when plotting the above function; reduce the amount of time remaining on the loan (from the first repayment payment of 300 monthy units @ t=0 month to calculate principle & interest to 299 monthly units @ t = 1 month for example). So reduce the time steps in each entry (spreadsheet cell) by either monthly or weekly interval and so on. As the loan time frame becomes shorter the slope of the amount owing on the principle becomes steeper. This is due to your ratio of the amount owing due to interest in comparison to principle is reducing. [/QUOTE].

[dstebbins]

Did no one read my last post? I've gotten the problem solved already. It's in the parenthases.

A mod can lock this, now. I'm unsubscribing.

[Arron93]

Tremendous concepts on this web site. It's rare these days to find websites with data you are seeking. I appreciate the information your providing. I will certainly bookmark it or even register for your rss feeds simply to be updated on your new posts. Maintain up the nice job and I'm sure some other folks researching valued information will actually stop by and benefit from your site for resources.