Free Microsoft Excel 2013 Quick Reference

- Repeat calculations
- Loan calculator question
- New user with a mortgage formula question
- Calculating starting balance
- Repayment Calculator
- Trying to Manipulate a Mortgage Payment (HELP!)
- Debt Reduction by One Time Payment In Amortization?
- Date / Linking question
- Calculate Total Returns
- Formula calculation
- Loan Amortization Schedule round down cell with an exisitng formula
- Financial Modeling Debt Calculation Problem
- Calculating finance charge on auto loans
- FV Function. Final Balance Of Savings
- Calculating Monthly Investment
- Vlookup help with multiple values
- Negative value lookup formula
- Recurring monthly payment
- Calculate APR from ARY
- Principle reduction question

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?

Thanks.

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!

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

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

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?

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

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.

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

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.

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.

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.

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

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

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.

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!

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