# Interest Calculation On Loan Compounding Yearly Want For Selected Months

Jan 26, 2014
I have created a excel sheet here i want the total interest charged for three months in 3rd mnth interest charged column, if i select 7 mnths term total interest charged for 7 months should come in 7th month interest charged colum, if it is 13 months total interest for 12 months in 12th month interest column and remaining 1 month interest in 13th month interest charged column

INTEREST CAL.xlsx

View 1 Replies
ADVERTISEMENT
Aug 27, 2008

I have a calculation I do that calculates a clients "effective interest rate" if they make extra payments towards principal.. Calculation works fine.. However, I am now trying to figure out how to amend that code if it's an interest only loan, anyone have any ideas?

Here is the effective rate calcs on a random normal amortization loan:

this is in B2, and answer is 7%

=RATE(B4*B5,-((B3+B7)/B6),B7)*12

B3 = Total*Interest 279017.8

B4 = #*Years*in*Loan 30

B5 = #*Payments*/*Year 12

B6 = Total*Payments 360

B7 = Beginning*Principal 200000

B8 - Ending*Balance 0

problem is when someone is on an interest only loan they pay more interest than a normal amortization because they are not reducing the principal in the first x number of years. So I need to compare the interest only effective rate to an interest only loan.

Here is the example I'm working on... A client's loan is the following:

Loan amount - 131,538

interest rate - 6.15

30 year amortization

10 years interest only

normal client would pay an interest only payment of 674.13, then after i/o period would go to 953.80 for last 20 years of the loan, and they'd pay about $178k in interest.. Now if that client pays an extra 1,000 per year, I can calculate the amount of interest they'd accrue, but have no clue how to back into the "effective interest rate", basically that says you are paying the same amount of interest as someone with a x.xx% interest only loan.

View 9 Replies
View Related
Apr 9, 2014

I have a created a Data Chart Below. A - C are the columns and 1-7 are the rows. I have hard-coded the equation in cell B5 that I am using.

What I want to do is input an number into B3 that automatically makes Cell B5 equal to Cell C1. Is there a process in excel you can use to do this. Or do you just have to use trial and error?

A B C

1Loan Amount$10,500,000 0.0730041581143804

2Term 10 years

3Rate

4Amortization30 years

5Constant (K) =PMT(B3/12,(B4*12),-1000)*0.012

6Annual Payment$766,544

7Monthly Payment$63,879

Example is attached.

View 1 Replies
View Related
May 6, 2008

attached the spreadsheet that I need help with in that same thread. Please check out the spreadsheet via: [url]

Here's my issue:

I'm trying to be debt free roughly around 10-11 years years based on my current plan. SO, what I am trying to do is figure out what I can do with my savings at 1, 2, and a 3 % rate of return in a savings account that is compounded monthly after that debt free point. My time frame for results are 5-10-15-and 20 years.

View 9 Replies
View Related
Dec 10, 2007

I am trying to set up a budget for my self. Of course I am trying to make it as complicated as possible.

I have done searches online and that is how I found this forum. I am finding amortization formulas, but not what I want

Here is my question.

I have two fields, Mortgage and auto loan

I want to have a field that does a calculation for me.

I know following fields as an example making numbers up

Interest rate is fixed at 5.5%

Amount left on loan is 150,000

Loan is fixed at 30 years with 28 years left

I want to run a formula so when I make a monthly payment of 1000 dollars how much of that goes to interest, how much to principle (not to make this complicated even more, but let say that I have 200 escrowed which I am not sure if that has interest calculated on(I don’t think so as it is property tax?))

Same example for car loan.

5 year loan, 4.5% interest, payment 438 a month.

View 9 Replies
View Related
Feb 20, 2014

If i borrow Rs.50000/- at 7.25% interest compounding monthly, repayable in 4 quarterly instalments, when i put it in excel using pmt function for emi quarterly payment and ipmt for interest calculation at the end of the 4th quarter (i.e last instalment) the balance will not become zero it shows a balance of Rs.14.35 its due to interest compounding monthly, is there any formula in excel to overcome it?

View 2 Replies
View Related
Nov 10, 2013

With the data table given below, how can I formulate the yearly installment based on the tenure. Like below example.

Name

Loan

Yrly Pymnt

No. of Tenures

Y2014

Y2015

[Code] ........

View 3 Replies
View Related
May 29, 2014

What I am trying to do is to create a formula for the attached spreadsheet - that calculates the daily compounding interest based on the higher rate of the two rates for the first five years then after 5 years the calculation should only be based solely on the blocked rate.

View 5 Replies
View Related
Jan 31, 2014

I currently use goal seek to calculate an interest rate for a loan product. My problem is i would like to have the same function but not through a goal seek. In goal seek i have to set the value i want to achieve but ideally i want it to calculate automatically

I have attached a workbook with details. I use a loan amortization schedule to calculate the interest from parameters set on sheet 1

View 2 Replies
View Related
Aug 2, 2014

I have been loaning my brother money over the past 14 months. The loans have been in the form or $1000 per month plus random payments for one-off expenses like doctors fees. He's not paid anything back yet but we want to know what the total owed is for interest of 10% per annum.

I can easily create a table with payments I've made and the dates with a running total of how much I've paid but how to I create a running balance of what he owes over time based on adding in interest. This might end with a one-off payment in a couple of months, I'd like to calculate what is owed there as a minimum.

View 7 Replies
View Related
Sep 5, 2012

I need J22 to multiply based on years in B22 AND increase 5% for each of those years (compounding) after two years (excludes year 1 from 5% increase). In addition the cell needs to remain blank if D22 is blank. B22 = 1, then the stockprice needs to remain the same, and only increase by 5% after year 1.

Currently...

B22 = a number of years indicated by the formula: =IF(A22="","",DATEDIF(A22,I3,"y"))

J22 =IF(ISNA(VLOOKUP(D22,stockprices,2,FALSE)),"",VLOOKUP(D22,stockprices,2,FALSE))

Example:

If J22 stockprice lookup is $1000.00, and the number of years listed in B22 is 6, then the reported value in J22 needs to be $1494.40.

Windows 7 Ultimate / Excel 2010

View 1 Replies
View Related
Mar 20, 2008

I'm trying to find a formula that will give me the following:

Rent is $2.00 a month with a 3% escalation each year. What is my Rent after 5 years?

Now the other thing is that I need the number of years to be a formula that counts the number of columns with data up to the column with $0.00.

View 9 Replies
View Related
Apr 28, 2008

I have a macro that formats a spreadsheet to show outstanding invoices, grouped and subtotalled by month. To add to this I need VBA code that will use the subtotals to calculate interest on overdue accounts.

Interest becomes due a calender month after the month in which the invoice is dated. So for example a January invoice would start to accrue interest on 1st March.

Below is the subtotals code (sadly the totals don't adjust if data is added or removed but perhaps that is another question for another day.)

Dim LastRow As Long

Dim NextMonth As String

Dim R As Long

Dim Rng As Range

Dim SubAmount As Currency

Dim ThisMonth As String

Dim TotalAmount As Currency

Dim Wks As Worksheet

Set Wks = Worksheets("Reconciliation")

LastRow = Wks.Cells(Rows.Count, "A").End(xlUp).Row

Set Rng = Wks.Range(Cells(2, "A"), Cells(LastRow, "D"))

View 9 Replies
View Related
Mar 4, 2014

I have a capital lease amortization schedule with annual increases to monthly rent that I am trying to solve for an interest rate such that the balance nets to zero at the end of the term. I am calculating on a monthly basis; in other words, principal minus monthly payment plus monthly interest expense equals ending monthly balance is calculated in each row each month. The present value is known, payment term is known, future value is zero, and the payment amounts increase annually. These assumptions may change for new leases so ideally the solution would be dynamic, adjusting for shorter/longer terms, etc. Here is an example of my assumptions:

Payment start date: 8/1/13

Term end date: 10/31/2025

Rent length in months: 144

1st months rent: $500,000 - payments are due at the beginning of the month and are paid monthly

Annual rent escalation: 3% - i.e. 1st 12 months is $500k/month, 2nd 12 months at $515K, etc.

Beginning NPV: $75M

Ending value: $0

Imputed annual interest rate: UNKNOWN

I'm not sure if this is relevant, but the monthly payment is allocated between principal and interest. Monthly interest expense is calculated as the current balance * (imputed interest rate / 12).

Currently, I've plugged the interest rate such that my ending balance is 0, however I was hoping to calculate it on the fly as opposed to manually plugging it.

how to calculate annual interest rate with these inputs? Is there a way to make the rate function work with payment increases?

View 7 Replies
View Related
Nov 22, 2009

in calculating the no of months in below scenario.

Fiscal year 2009 comprise of Jan 2009 to Dec 2009

Fiscal year 2010 comprise of Jan 2010 to Dec 2010

Fiscal year 2011 comprise of Jan 2011 to Dec 2011.

For example I have a period starting from Sep 2009 to Feb 2011

I need formula for the calculation of number of months in each fiscal year.

In above example number of months in Fiscal year 2009 will be 4, in 2010 it should be 12 and in 2011 it should be 2 months.

Structure of file

Starting period is mentioned in column A and Ending period is mentioned in column B. Fiscal year 2009 in column C, 2010 in Column D and 2011 in column F.

View 9 Replies
View Related
Mar 24, 2009

I have a spreadsheet that each month, we populate a new row of data. The rows are already set up in the spreadsheet, but we just populate the new row.

We are calculating a rolling 12 month total. Each month, we have to modify the formula below to pick up the last 12 months.

For example, next month we will populate data into cell M91, then we need to manually modify our formula to read M80:M91. Wondering if there is a way to have the formula below to look at a range, such as M100:M1, and count the last 12 months? This would eliminate us having to change this each month on several spreadsheets.

In Summary: I would like to replace the M79:M90 to count the last 12 months instead of changing the formula each month.

Here is the formula:

=(FVSCHEDULE(1,M79:M90/100)-1)*100

View 2 Replies
View Related
Mar 15, 2007

In a financial environment we have a calculator which uses iteration to allow for a cost being added to loan amount where the cost is based on the total loan amount. Iteration is set to 100 iterations with max change .001

On one PC the first time the calculator is opened it gives a particular (incorrect) result. If the input cells are cleared and the data re-entered, it gives the correct result. This only happens on one particular PC. Is there some other setting , other than the iteration setting, that would cause this?

View 5 Replies
View Related
Nov 30, 2011

I want to sum a certain criteria and for the selected months.

Example:

Column A January February March Total

Pen 20 30 40 90

Pencil 10 10 10 30

I want to have total of Pen for the months of Jan and Feb.

View 2 Replies
View Related
Oct 24, 2011

how can I provide a chart that display as a line chart twelve months of data for the item selected starting or ending on the data that I have selected

View 4 Replies
View Related
Dec 29, 2013

I have a problem calculating a total interest for an input period that has different interest rates. You can see the attached file: INTEREST.xls

I have used a function but the result comes out wrong. Can I fine tune it or do I have to change my method of calculating.

View 10 Replies
View Related
Jul 30, 2006

Cells A1 through A12 contain a series of cash flows:

-100000

-25000

-10000

4000

12000

1000

18000

-4000

12000

10000

15000

95000

In order to calculate the annual IRR the formula should be =IRR(A1:A12)*12). The result is a 25.49% IRR. However, I've seen a formula that calculates the IRR in a different way. In this case, the formula is =(1+IRR(A1:A12))^12-1. In tis case the formula yields a 28.69% IRR. What exactly is the second IRR formula calculating? Is it compounding the monthly returns differently than the first formula? Which calculation is more accurate, and under what conditions would you use the first vs. the second formula (and vice-versa)?

View 4 Replies
View Related
Mar 3, 2014

In the attached file I have a pivot table created from the data in A:D.

By default the pivot table sums the values for each product (e.g. 18.00 for the sales of Product A). Instead of adding the values for each product I would like to get the pivot table to compound the values for each product. In columns J and K I have shown the results that I would expect when compounding the values of sales and profit for each product. Data for more dates will be added in A:D.

how to achieve this using a pivot table.

View 2 Replies
View Related
Apr 4, 2008

I work for a small company and we are attempting to change the way that employee's vacations are calculated. My Excel knowledge is just enough to get me by. I am building a simple spreadsheet for each employee. We want to make their vacation time roll over each year on their anniversary date. In prior years, we would just use January 1 after the employee had been there a year or two. My question is can Excel take the employees start date and add the additional hours that are earned on the anniversary date automatically.

View 9 Replies
View Related
Jul 16, 2014

I have a set of 4 values that I would like to show in a graph. The values I need graphed are as follows:

Brought Forward = 3

New Requests = 2

Closed Requests = 4

Remaining = 1

These 4 numbers will be part of a monthly report (so I only need the numbers graphed once) and also a yearly report (so I would need to find a way to graph all 12 months).

View 1 Replies
View Related
Dec 6, 2013

I have a date column (Column E: Date Entered) on my spreadsheet that I need to set conditional formatting on. There are two conditions:

1) 18 months from the date in the cell needs to be highlighted yellow

2) 24 months from the date in the cell needs to be highlighted red

View 6 Replies
View Related
Jun 25, 2014

I have a challenge trying to get excel to recognize yearly intervals.

Let me explain further:

Interval Percentage Yr1 Yr2 Yr3 Yr4 Yr5 Yr6 Yr7 Yr8 Yr9 Yr10 Yr11 Yr12

3yrs 10%

I want excel to insert the 10% into the cells every 3yrs.

I have attached a dummy workbook detailing my issue

Interval problem 1.xlsbâ€Ž

View 2 Replies
View Related
May 2, 2014

I'm using the code below to extract data from a 'Source' sheet to populate a "Yearly Extract Summary" 'destination sheet. With the unique distinct values copied from column I on the 'Source' ("All Data") sheet to column B on the 'Destination' ("Desired Output") sheet. In addition the values from column J on the 'Source' sheet are summed and paste under the relevant month on the 'Destination' sheet.

[Code] .......

The code works fine and the correct figures populate the correct columns and rows on the 'Destination' sheet.

As you can see from the code above, the monthly values have to be hard coded to match the column headings and this is fine when using a static 12 month period. But I'm now wanting to use a rolling 12 month period, which, at the moment, necessitates the need for me to change the code each month so I'd like to change the code but unsure where to even begin, how to produce the initial script.

I'd still like maintain the existing functionality in this section of code:

[Code] ...........

I have attached a file which contain 3 sheets.

The "All Data" 'Source' sheet,

The "Output" sheet, used for testing, and

The "Desired Output" sheet which shows the results using the current code

To run the code, please use the button at the top of the "All Data" sheet.

Sum Categories Test2.xlsâ€Ž

View 14 Replies
View Related
Apr 7, 2009

I wish to have a simple planner displaying the academic year with only mondays' dates.

ROW 3 displays the dates

Column B is Week 1

Cell B3 is the first date (10.08.09)

Cell C3 should read 17.08.09

Cell D3 should read 24.08.09

ETC

I have managed to do it using autofill before but I can't get it to do it now. Is there a preferred way to achieve this?

View 5 Replies
View Related
Jan 5, 2012

I have a column that contains dates for an event. I would like to tally quarterly and yearly totals for these dates. What formula can I use to accomplish this?

View 1 Replies
View Related
Nov 30, 2009

I owe 15462 in the bank, currency dont matter here, that is what I owe right now, but I want to have a cell in the frontpage with the amount left, so can I make a line called =remaining-each month

the amount should then each month be substracted from the new month and so on, until the amount is 0

can this be done?

the second page in the spreadsheet has a post with monthly pays to the bank ...

View 11 Replies
View Related