Free Microsoft Excel 2013 Quick Reference

Need formula to calculate payment and interest Results

How can I perform a series of what I can only describe as circular calculations? What I mean is I want to perform a calculation which creates a particular result. I then want to perform that same calculation using the newly created result and I’d like to perform that process a certain number of times based on input from the user.

I also need to keep a running total of certain amounts calculated along the way. The calculations I want to do stem from a basic amortization calculation and I’d like to be able to calculate how much principal has been paid after a certain number of payments are made, same with knowing how much interest has been paid.

LoanAmt $180,000
IntRate 7%
PerYears 30
NumPayYr 12

Using the –PMT(rate, nper, pv) formula, is it possible to keep track of how much interest and principal has been paid after 30 payments with one or two cells of formulas?

i need a formula to help me calculate a loan amount. I have the payment, the interest rate and the amortization period and i want to calculate the total loan amount. Basically, i am trying to work backwards from the PMT formula but i don't know the formula i should use. Any direction on this would be great.

Thanks.

Hi,
I need help in constructing a mortgage loan formula whereby the Payment is known, the Interest Rate is known, the Term (# of years) is known, but the Principal Amount of the loan is unknown.

To better illustrate my question, I will first calculate the monthly payment for a $100,000 mortgage loan for 30 years at an interest rate of 6 per cent:

cell A1= 6% (the interest rate)
cell A2= 30 (the number of years)
cell A3= $100,000 (the principal amount of the loan)
The answer in cell A4 = $599.55

I got that result ($599.55) by typing the following formula into cell A4:
=-PMT(A1/12,A2*12,A3)

My question is this:
What is the formula I can type in to find out what the Principal Amount of the loan would be if I already have the interest rate (A1), the number of years (A2), and the monthly payment (A4)?

I'm stuck on this. Any help would be greatly appreciated!

We are putting loaned funds in escrow to pay the first five years of a loan, and this $ will earn interest (bank will only provide entire loan payout insted of providing funds ad hoc). We want to end with close to or $0 in escrow. What I need is to automatically calculate how much money we need in escrow to cover 60 payments with the escrow balance earning interest. Let's say our payment is fixed at $1,000, and the interest is fixed at 6.25% compouned monthly after the payment. I can manually calculate that I need to start with $51,683.63 to pay off the $60K in payments, but I need to be able to constantly manipulate these numbers. Should I be using FV or PV for this, something else, or is there no special formula for this type of calculation?

Hi folks. I've created a spreadsheet that is used to calculate loan repayments.

It's based on an online calculator, and the figures in the spreadsheet have to match the online results. The trouble is I can't match it exactly and need help with this.

The calculator works out 2 things;

1. What the loan balance will be after 25 years (300 months) using a fixed monthly payment.

2. What the loan balance will be after 25 years (300 month) using an increasing payment based on an increasing salary.

I'll try and explain what it does at the moment;

The inputs are Salary (A), Loan amount (B), Annual Interest Rate (C), Salary Increase rate (D), Fixed Monthly Payment (E), Increasing Monthly Payment (F1-F25) i.e. one for each year.

I can work out what the fixed monthly repayment amount is based on the salary. And what the increasing monthly payment will be based on an increasing salary. The formula to work out the increasing salary is A*(1+D)^1, and then from that I work out the monthly payment. This part works ok.

The part that I can't get to match is the decreasing Loan balance e.g. what the balance would be after deducting the monthly payment, and adding the interest for that month.

The formula I'm using is;

(B-E)+((B-E)*(C/12))

This is worked out for each month, and the Loan amount for the 2nd month is the balance at the end of the previous month, and so on.

For increasing payments, the Formula would be;

(B-F1)+((B-F1)*(C/12)) for all of year 1, then F would increase for year 2, 3 and so on.

I am doing this right, is there a more accurate way? Or is there a way I can improve this method to get it to match what's getting done online.

The formulas in the spreadsheet are the same as what the code of the online calculator do.

thanks

I'm trying to prove how fast a mortgage can be payed off by applying
the next months principle payment to the current months payment. I
need help writing the formula. I have found many calculators that
will give your payment and such but I just cant figure out how to
write it.

Here is what I want to do.
$100,000 loan at 6.00% interest for 360 months
I want to be able to show her how much the payment will be. How much
of each payment will be interest, and principle. Then show her in a
formula if we were to apply next month principle, how much the payment
will be and how fast the loan will be payed off.

This is all I have been able to figure out...

Cell B2 = [=B1*((1+0.06/360)^30)-C2]

B1=Loan Amount
C2=Payment (have to guess at the payment)

Thanks for any help given!!
DC

How to calculate a One time Payment to maintain the Tenure In Amortization?

Ex:-
I havea loan of Rs 1,00,000/- to be paid @ 8%/12 for the first year and then from 13th month , it will be Floating Rate of Interest which would be mostly higher than 8%/12...

I will be paying this loan off in 5 years ( i.e. 60 months)
and the EMI = Rs 2028/- per month..

Lets say, if the Interest goes up to 12%/12 after the 12th EMI then the tenure increases by 5 months from 60 months to 65 months thus adding to the cost of Rs 2028*5 = Rs 10140 more..

Now What amount do I need to pay alongwith the regular EMI's to maintain the tenure of months?

The amount Im referring to is a One-Time Payment added with the EMI for the 13 the mOnth to reduce the Principal and accordingly the tenure?

IS there anyone who can help me with an explanation as well as a formula in getting to this?

I have two worksheets - one (that I'll call "A")that calculates total interest owed daily on a loan (with additional incurred expenses along the way). The interest is calculated daily (from the inception of the loan to the current date).

The other worksheet ("B") contains a list of owners who are responsible for these payments.

What I need to do is have "B" find today's date
in "A" (which I've already added as a formula to the worksheet) and then go over to column C and return that value to worksheet "B."

The goal is that any time I look at Worksheet B - it will always be current with the interest amount accrued to that day.

I hope this makes sense!! LOL

I assume that there is a way to do that but I'm clueless as to how. Any help would be GREATLY appreciated!!

Hello this is my first post and hope I am in the right sub-forum. I apologize in advance for a long post, but hope someone can help me.

I am generally pretty good with excel but something has me stumped and I feel there is an easier way than what I am doing.

Essentially, there are three columns of data that I am interested in at the moment within each row (Lets call it A, B, and C). Each of the columns will be a dollar amount. A will be entered before B and B before C as they are to track subsequent payments. The interesting thing is that the value in the columns will dictate what the interest will be and thus the "Total Return" as I am calling it. As such, I have elected to use IF statements.

A little more background before I dive into actual excel speak; the sum amounts in A, B, and C will generate interest at different levels (Lets call the sum of A,B, and C = D). The first $1,000 will generate interest that compounds daily of an annualized rate of 3%. Any amount above $1,000 will generate interest that compounds daily of an annualized rate of 7%. [These numbers are for example purpose and hopefully clear enough to be illustrative on how to formate a formula, my actual numbers are needlessly complicated for this exercise... sig figs always inviting themselves to the party]. Anyway, I am looking for a way to input a formula so that excel recognizes that once previous payments reach $1,000 to start using the new interest rate (7%) and apply it to any subsequent payments if applicable.

I was able to use this IF statement for column A:

=IF(ColumnA>1000,(1000*((1+0.03/365)^(365*(Months to Payment/12))))+((Column A-1000)*((1+0.07/365)^(365*(Months to Payment/12)))),(Column A*((1+0.03/365)^(365*(Months to Payment/12)))))

Where the bold text indicates arbitrary positions on the excel sheet. I know there has to be an easier way, however, the interest calculator built into excel does not seem to suit my needs as an end date is required. As you can see from above, the end date is unknown to me at the moment as no maturity date is established. Instead, I built a formula to recognize the start and end dates of the transaction to establish "Months to Payment".

Besides being overly complex, the above formula does not address B or C at all. I tried tailoring IF(AND( and IF(OR( statements to consider A, B, and C at the same time but my first 3 or so tries didn't work as I wanted them to. Not to mention, I have no idea how I could make the sheet realize that for a hypothetical $500 payment in B, perhaps $136.90 needs to accrue 3% while the remaining $363.10 should accrue 7% (depending on where A puts me at the $1,000 transition point). It would be easy if I knew A would always be either above or below $1,000, yet I have no idea what it will be and could be anything from a few dollars to a couple thousand dollars.

I will try to prepare a sample sheet now and upload it shortly as it is most likely easier to understand by actually seeing it. If everything above could be settled, I would be very happy. I will add 1 more detail below that complicates the situation. However, I value a formula to address the issue listed to this point much more than the complication I will list below.

Complication:
A, B, and C are all quantitative payments. Imagine X, Y, and Z qualitatively describe the type of payment A, B, and C are (where X describes A, etc down the row). There are three options for the types of payments X, Y, and Z can be: 1, 2, and 3. 1 and 2 have no effect on anything and are strictly for more precise record keeping. However, option 3 demands that the interest automatically be calculated at 7%. For example, imagine if 1 = Food, 2 = Gas, 3 = Entertainment; any column (A, B, C) described by 3 would accrue 7%. Again, this complication is secondary to my first concern but would be awesome if I could automate the whole thing.

I will put together a sample sheet now. Thank you in advance for all your help.

Is there a way to setup multiple formulas so they calculate in a specific
order?

I have a complicated financial model that we use for budgetting. After we
change assumptions, we get errors in our formulas. What we have to do, is
trace the cells back to the error (using the auditing tools) and delete the
formula calculating the error and then undo the deletion. Only trouble is,
you have to do that many times to get everything calculating properly.

Problem is created by the following: we are trying to calculate net income
after tax. We need to calulate interest income(expense) based on the monthly
cash position which in some cases is calculated on the cash position after
the tax payment. Only trouble is, tax is calculated on net income before
tax, which is after interest income. Understand the dilemma? I'm hoping
somebody has a solution they could suggest.

I created a loan schedule to track my auto loan payments and the extra payments towards the principal, however the scheduled payment field is rounding up and shows the incorrect monthly payment, so I need it to round down. The field already has a formula in it: =IF(Values_Entered,-PMT(Interest_Rate/Num_Pmt_Per_Year,Loan_Years*Num_Pmt_Per_Year,Loan_Amount),"")

I can not figure out how to make it round down due to that. I tried =ROUNDOWN(I5,2) in the cell next to it to reference back to that cell but it did not work.

Due to this number being wrong, it is throwing off my total interest that is set up to calculate with a formula as well as the monthly interest fields.

How do I fix this? I do not know how to create 2 formulas in the same cell. When I tried with an & and the round down formula I received an error.

I'm trying to calculate the required debt needed to cover costs as well as interest payments on that debt.

Here are the conditions:

Amount Needed to Finance: 485500000
Debt Conditions:
- 4% annual payments (interest only) over 5 years
- Need total debt to equal 0 after 5 years

So, Interest + Amnt needed to finance = Total Debt Required.

I used a formula to get the correct answer for these specific conditions, coming up with 485500000/(1-(.04%*5))= 606875000, which is correct. However I need a formula that allows me to change any condition and get the answer. In this case, if the percentage =>20% the i get the wrong answer. Any help would be greatly appreciated.

I've been trying to figure out how to calculate finance charges on auto loans for over a month, and I still have yet to figure out how to make a formula work. Has anyone ever done this before? Auto loans calculate interest on a daily basis, so days to first payment come into account. I cannot seem to get a functioning formula. Any help would be really appreciated, or even a place to start. Here is some data I have to work with to build a formula:

contract date: 05/03/11
first pmt date: 06/17/11
Term: 60 months
Payment: 472.01
Interest Rate: 17.99
Amount Financed: 18453.57

And finally, I know that the finance charge for this loan is $9867.03. What I'm trying to do is calculate participation, which means I need to calculate what the finance charge would be at 15.99%, and so forth. All the information is there, I just can't seem to figure it out and I'm pretty frustrated at it. Now, I know that the actual interest rate is closer to 18.02% from my company loan calculator, but I can't get it perfect.

I need to calculate the final balance of a savings account. Thought this could be done using the function FV. However I had not used this function before. Therefore to make sure that I had set up the formula correctly I checked it manually with a small example. The results are set out below. You will see the function FV fails to include the final interest payment. (£2620.32 - £2472.00 =£148.32)

Where is my mistake?

The data to be used will be:
A regular payment of an identical amount on first day of every year.
Compound interest calculated annually and credited to account on final day of every year.
Period of years.

Example of Saving Regular Amount every year at Compound Interest
Using =FV(A1,A2,A3)

Amount of Saving per annum £1,200.00 A1
Rate per Annum 6.00% A2
Period of Years 2.00 A3
£2,472.00

Manual calculation of example:

Payment at start of first year £1200.00
Interest at end of first year 72.00
Balance at end of first year 1272.00
Payment at start of second year £1200.00
Balance at start of second year 2472.00
Interest at end of second year 148.32
Balance at end of second year £2620.32

Thanks for any advice you can give me. My knowledge of Excel is very basic.

Hi!

If I want to obtain a future value of $500,000 at the end of 20 years, how much do I need to save each year at an interest rate of 10% per annum?

The installments are made annually in advance.

I ended up using the following formula: =PMT(10%,20,-PV(10%,20,,-500000,1),,1). Let's define (Insert/Name/Define) the answer to this function as Pmt1.

By then using =FV(10%,20,Pmt1,,1) to confirm that Pmt1 will end up providing $500,000 after 20 years I get the answer I am looking for but have absolutely NO IDEA why it works.

Worse, I do not know whether it is the correct answer.

I have the following function (courtesy of someone) that I use to determine the expected future value of a series of annual payments at a fixed interest rate but also with fixed annual increases in the payments. (Example: $1000 per annum is invested for 20 years. The interest earned on the $1000 is 10% per annum. The $1000 increases by 5% each year - i.e. 19 increases)

=Pmt1*SUMPRODUCT((1+5%)^(ROW(OFFSET($A$1,0,0,20,1))-1),(1+10%)^(20-ROW(OFFSET($A$1,0,0,20,1))+1))

Assuming the payment does not increase, I simply replace the 5% with 0%.

When I run this function and use Pmt1 as the annual payment the answer differs from the one that I get from the PMT function that I quoted above until I change the ",,1" in the function to ",,0".

What do I not understand about these functions!? Which is correct or are both provided I learn to know what they do?

This is the vaguest question I've ever been able to devise simply because I can see that something is amiss and I do not know what - or how to start figuring out what it is that I "know not"!

Can someone simply please look at what I am trying to achieve by starting a conversation about the two functions and how they relate to my issue?

Thanks!

Harry

Okay, I am lost. I am working on a spreadsheet that needs to come back with a total value for lease months. So I have on sheet that has 56 leases on it with about 4124 lines. I need to create a formula that calculates from the Lease months for each lease to total for the month. So some leases started before others. For example lease 1 starts 4/2006 - 10/2008 and lease 2 starts from 5/2006-11/2008. I need to create a formula on my new summary page that looks up by the date in each lease and calculates the total payment for that month.
Lease 1
Payment

Payment70750-xxxSalesTotalPrincipalDateAmountInterestPrincipalTaxPrincipalBalanceAt Inception35,240.00 4/2006729.00 262.27 412.73 54.00 466.73 34,773.27 5/2006729.00 258.81 416.19 54.00 470.19 34,303.08 6/2006729.00 255.33 419.67 54.00 473.67 33,829.41

Lease 2
28039-001PaymentPayment70750-xxxSalesTotalPrincipal DateAmountInterestPrincipalTaxPrincipalBalanceAt Inception35,240.005/2006729.00262.27412.73 54.00 466.73 34,773.27 6/2006729.00258.81416.19 54.00 470.19 34,303.08 7/2006729.00255.33419.67 54.00 473.67 33,829.41 8/2006729.00251.81423.19 54.00 477.19 33,352.22

Summary sheet for all leases
PaymentPayment70750-xxxSalesTotalPrincipalDateAmountInterestPrincipalTaxPrincipalBalanceAt Inception4/2006This is where I need the formula5/2006.6/2006

I need assistance with a formula,

Possibly to combine If statement and lookup stmt. I have a spreadsheet that calculates principle, interest, and combined pmt amount based on certain amortization and payment frequency. In one cell I would like the expiry date to pull in (the expiry date would be the date that the fund balance would be at zero ie. all payments have been made and principle is nil). I am trying to make a formula that will search down the payment column and when it sees a negative number or zero, it will fill in the expiry date (which is in a second column) that is attached to that figure.

I hope this makes sense to everyone! I am looking for any help I can get! Thanks.

I posted this once today already, but I can't find my original question
anywhere!

I need a formula which will enter a loan payment on the same date each month
over a 30-year period. I have a long spreadsheet which calculates the
interest on a daily compound basis, using one row for each day. This needs
to take into account months with 28/30/31 days, and also leap years.

Any help will be much appreciated!

Anyone:

I can find out what an APY is with Excel,with this function:

=POWER((1+(A1/B1)),B1)-1 where A1 is the Rate and B1 is compounding
frequency.

I put this formula into any cell on a spreadsheet (except A1 or B1). In
cell A1, I put the stated annual interest rate - in decimal format.
For example, if the stated annual percentage rate is 10%, I will type
".10" in cell A1. Then, I put the number of times I compound each
year. For example, for daily compounding I would enter "365" (or
360 depending on the institution) in cell B1.

In the example I've used, I will find that the APY is 10.5156%. In
other words, if you get 10% annually with daily compounding, your APY =
10.5156%. If I change the compounding frequency, you will see how the
APY changes. For example, I might show quarterly compounding (4 times
per year) or the unfortunate 1 payment per year (which just results in
a 10% APY).

Now, what I need, is HELP. I need a formula that will convert the APY
into the APR.

So like the above example, I would like to be able to type in the APY
as 10% and have Excel show the APR of 9.6455%. The reason for this
request is a complete spreadsheet already built with quarterly
compounding. I know that a percentage of 9.6455% APR entered will equal
10% APY. I need a formular to show this results for various ARP's and
compounding frequency's

Thank you for your help

I know how do use the pmt() function to calculate mortgage payments but
I need to know the easiest way to calculate the total principle
reduction/remaining balance after a set number of payments without doing
an amortization table.

For example: I finance $500,000 for 30 years at an anual interest rate
of 6% making monthly payments. After 3 years (36 payments) I sell the
asset for $650,000 and want to pay off the remaining principle balance
of the loan. How do I calculate that amount in a one cell formula?
Thanks for the help!!!


No luck finding an answer? You could always try Google.