I'm trying to make an amortization table with all the individual payments. I'm trying to avoid putting in the formula manually into each individual cell. I have a formula = ROUND(F11*0.12/12,0) and I want to insert that formula down an entire column, but increasing the F11 to F12, F13, etc.
View 4 RepliesI have uploaded a sample amortization schedule.
1. I require the table to adjust itself based on the loan period and number of payments per year entered in D14 and D15 respectively.
2. Also, if a value is entered in column E, then i require the whole table to update as well.
I am trying to use the if function in an amortization table and I am wondering if there is a way to make it so if I cut the years in half if I can make the table read with 0's or dashes rather than to start counting back up again.
View 9 Replies View RelatedI need help creating a formula. I have an amortization table and I need to take each months information and place it into a cell which resides in a letter. In the end, there should be 180 pages with different dates, interest, principal and total payments. I know that there is a way and I can not figure it out.
View 9 Replies View RelatedI have a sheet (like example attached). it's broken out by month for many decades. certain columns like B-N need each field populated. There are others like O, P, Q, where I need to delete all cells except for 2 months that year (in this example, all except May and November).
Is there a shortcut method to do this quicker?
example.xlsx
I am working with an amortization table and need to work out the elapsed time to amortize the loan.
Currently the worksheet is working fine and calculates the end date of the loan but the length of the column varies depending on the scenario.
I need a formula to calculate the time that elapses between the first date and last date.
The data starts in cell b13 and the last date could appear in pretty much any cell below that, so the formula will need to look for the last valid entry.
I am using excel 2003.
I set up a formula knowing only two variables?
1. Assuming I know how much per month one can pay in total, and
2. The percent interest rate
... I need the total amount that can be financed.
Example:
A. $3,900 maximum per month
B. 5.00% interest rate
C. = x (the amount to be financed)
Scenario - I started doodling with buying a property to let and then started thinking about adding the "shortfall" between the required bond payment and the income generated by the property back onto the loan each month as the shortfall arises. Let's assume that the financial institution will "pay" this shortfall from a previously agreed upon additional amount available on the property - understand?
While there is a shortfall between the income generated by the property and the bond payment, insurance, taxes etc., the outstanding capital will obviously increase as each new month arises and each specific shortfall is added onto the outstanding capital (loan at the bank).
Once the shortfall turns into surplusses, the surplusses are in turn paid into the account until the bond is paid in full.
With a normal, say 15 year, bond, the interest payments and principal payments of the constant mortgage payment (assume fixed interest rate) nicely works out to a balance of 0 in month 180, but not in the scenario I describe.
What happens in the scenario I described above is that, if I keep the payment constant (=PMT(and the rest - calculated on the original term and the original investment) each month's deficit is added to the outstanding capital but the payment stays constant. I want this payment to remain constant throughout the bond.........
I'm attempting to build a Mortgage payment calculator which calculates interest semi-annually. The goal of the calculator is to determine how much interest you would save if your payments were set to Accelerated Bi-weekly, and Accerlerated Weekly vs. Monthly.
I've already calculated what the payments would be for each payment type, however I'm not able to determine what the interest savings would be.
I've found this calc but it doesn't compare to my particular scenario/goals: http://dinkytown.net/java/InvestmentDebt.html. The major difference is that this dinkytown calc requires a new loan to be put in place and I am not in a position to refinance my current mortgage. My plan is to pay an extra $267 (b3) to the mtg (b4). It will take 128 months (b6) to pay off the mortgage. After that point, I need to calculate what the newly freed capital (b5) would do if I put in an account (i.e. a CD or a simple US savings account) that had an Annual Percentage Yield (APY) of 1, 2, and 3% for 20, 15, 10 and 5 years. I thought that I had the right formula in place for cells b7:b8 should the account earn a 0% rate of return, but I think it's faulty since it gives me a negative number for 10 and 5 year accumulation periods.
View 2 Replies View RelatedI have a column called Received Date (cell/column is format in dd-mm-yyyy that's found under 'Custom') which I make it into a pivot table, and GROUP it based on Year and Months.
Below is a look of it
-2012
Sep
Nov
-2013
Aug
Nov
-2014
Jan
Feb
Mar
Where the - sign is actually the expand of the year to see all the months in that particular year.
My question now is, how do I get the year from each of the month?
how do I get the "year" for the month of Nov in 2012 and 2013?
I need a lookup table. I have a table that shows what the sun is doing for each day of the year. Now I need to input longitude and time zone, add or subtract if the sun is fast or slow on that day
I need it to work off one formula not make 365 copies
When I group information in a pivot table by date, and select quarters or years, it is of course based on a calendar year. Can this be done by fiscal years?? using an addin or something, where I set the start month of the custom fiscal year.
View 5 Replies View RelatedI'm working on pivot tables using excel macro. Basically , I need to filter out the year submitted according to the current year . I did this to filter out my pivot :
For Each PivItem In _
PT.PivotFields("Year Submitted").PivotItems
PivItem.Visible = True
Next PivItem
For Each PivItem In _
PT.PivotFields("Year Submitted").PivotItems
Select Case PivItem.Name
Case "2012"
[code]....
But this can only filter out year 2012 . I need to use this workbook for the next couple of years and I dont want to keep modifying the codes .
I am looking for an amortization formula sheet for a boat loan that I have. That is easy enough, and I found that, but here is the hard part.
I went in on the loan with 4 different people all paying different prices a month. Let me get specific - I have a loan where one person is paying $55 a month, another $30, and three more $35 each. For a total of $190/month for the boat loan.
I want to be able to keep track how much each individual person owes in principal and in interest. Those prices a month are the minimum they will pay each month, but if someone decides to make more than the minimum payment, then it will mess everyone up with the total amount that they still owe.
So basically even though it is more one big loan, it is actually a total of 5 smaller loans as well with the same interest rate, but different minimum payments a month.
I have a sheet with following columns
Zones Month Revenue
Zone1 Apr-05 1111
Zone2 Apr-05 2222
Zone3 Apr-05 1222
Zone1 May-05 1451
Zone2 May-05 452
Zone3 May-05 4622
Zone1 Jun-05 891
Zone2 Jun-05 255
Zone3 Jun-05 1555
and so on all zones revenue each month.
I am learning Pivot table and struck at a point.
I am able to do simple pivot table but now want the following result
1. All the three zones in row fields.
2. Months in column field.
3. Revenue and running total of revenue (progressive sum) for all the months of desired financial years. ( I can get revenue and running total separately but I want these in alternate columns ie Revenue Apr-05, Revenue upto Apr-05, Revenue May-05, Revenue upto May-05 and so on)
Also how to get data for desired financial year only as 2005-06 (Apr-05 to March 06) or 06-07 (Apr-06 to Oct-06)?
I'm working with a loan amortization worksheet (downloaded from Office Online). Unless I don't understand correctly, the date formula on the worksheet doesn't calculate the way I need it to. I'm not totally sure what the formula they use is doing. It does use a lot of named ranges.
If a user inserts the total "Number of Payments per year", then I want the date to return the proper payment date.
For instance, If the start date is 1/1/07 and the number of payments per year is 24 then the payment dates should be like
1/15/07
1/31/01
2/15/07
2/28/07
It should be the 15th and the last day of the month.
If I put 52 as the number of payments then I want the formula to set the payment dates to every Friday.
I'm still learning formulas so bare with me guys. Attached is the worksheet.
I need to create an amortization table for several thousand loans. I could create a seperate amortization schedule for each loan, but as you can imagine, doing so would take a long time.
These loans have varying interest rates, loan periods, and beginning balances, so it is not as if I could just add the beginning balances together and amortize on that basis.
It seems like this will require a VBA macro, however my programming skills are a little rusty.
I am in need of a formula to calculate monthly straight-line depreciation assets based on the current month’s days.
So, if an asset costs $10,000 and has 15 years of useful life, in June this would calculate 15 divided by 12 divided by 31 times 10,000. I want to put cost in one cell and date in another, for the formula.
BTW, someone is using this formula, =(16736*(1/15)*(6/12))*31/184
There are various references and links to " mortgage calculators;" though they are specific to the US dollar. Is the formula still the same, irrespective of the currency and why does it come across as quite a complex calculation? i have been taksed with designing a "calculator" and don't seem to know where to start as the currency issue is confusing me.
View 7 Replies View RelatedI have a data table that contains begin dates, end dates, and colors. The data is input starting with the earliest begin date. On a second worksheet, I have a listing of the years starting with the year of the first begin date and ending with the end of this year. I need to be able to pull any color choices associated with a year from the data table on the first worksheet. Some years may not be specifically listed in the data sheet as the person could have had the same color choice for several years. Sometimes a person could have more than one color choice in a year.
I am at a loss for how to get to the data I need. I created an example spreadsheet to demonstrate the data I am working with, and an example of what I need my results to look like in the end.
Last week I posted a question related to formatting a cell to return a Day of the Week versus a numerical representation IE "Wed" instead of 02/20/2008 12:00AM. The solution provided worked for me:
1) Format cell to DDD MM/DD/YYYY HR:MN. Cell range (A1:A500)
2) Format destination cell with DDD. Cell range (B1:B500)
3) Destination cell (B1) = to original cell A1
4) B1 displayed data as "Wed"
However, the issue I still have is; I wanted to create a pivot table summarizing a year activity by Day of Week (in other words 7 entries for the year) and the pivot table still recognized all the MM/DD/YYYY. I ended up with a table displaying every day of the year instead of a yearly summary by Day of Week. Is there some way to strip out all the other numerical data from the new column I created to run a pivot table by Day of the Week for a whole years activity?
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?
I have a spreadsheet that has amortization tables for several loans. I am trying to add a summary tab that will pull the next due date on each loan. Some of the loans are behind so I can't use a >today() function. I have been trying the following:
=MIN(IF(AND(ISBLANK(Smith!A:A),NOT(ISBLANK(Smith!B:B))),Smith!B:B))
Column A is date payment is actually made, and column B is due date, so I want to return the first column B that has nothing in column A.
I am using Excel 2003 and I created an amortization schedule set up for an debt account. I am trying to pull the "Balance Due" from that schedule into another chart based on the current date (these are on two different sheets in a workbook).
For example, this is my 'Amortization Schedule':
Balance
Due
Interest
Rate
This Month's
Interest
This Month's
Payment
[code]....
And I am trying to pull the "Balance Due" from that schedule to place into this chart on my 'Debts' sheet: (based on the current date)
Name
Starting
Balance
Remaining
Balance
Interest
Rate
Minimum
[code]....
For example, if today were 1/15/13, I would want $3,796.34 from the schedule to go where the "x" is on the chart above. What formula would I use to accomplish this?
Also, on a side note, would there be a formula to have Excel pull the "Payment Date" from the schedule into the "Payoff Date" in the chart based on where the row has a $0 Balance Due?
I have two combo boxes: One for entering the Year, and one for the month. I can produce a message if the user leaves either box blank but I want a message to apear it the user selects a year AND month less than the current year (iYear) and current month (iMonth). I therefore need an AND statement between the two criteria but i dont know how to do it.
'....First Checks the Comboboxes arent blank then below Checks a future month/year secection is chosen
ElseIf YearBox.Value = iYear & iMonthbox < iMonth Then
MsgBox ("You may not enter Data before the current Month")
Else '...... Run main code here
Looking for a formula to compare current year values to previous year values. For example, if the current year has values for the month of January through March (100, 100 and 150), current year value will be 350 and the previous year value will be 975 (i.e. 300+275+400). The aim here is to make the previous year months summation equal to the present (or current) values. As new values are entered for the current year, the previous year's values will have to change to reflect the new month's value entered for the current year.
Month 2012 2013
Jan 300 100
Feb 275 100
Mar 400 150
April 650
May 454
June 800
July 500
Aug 375
Sep 525
Oct. 300
Nov 410
Dec 510
Sample file is attached : Comparison_Years.2011.xls‎
I am working on a budget for myself and want it to have running dates so the first data column will have the current month. I was able to succeed with this using the EOMONTH function followed by EDATE functions in the following cells, I then have these columns filled using a nested VLOOKUP MATCH function pair.
The problem I run into is with the months that extend into the next year, in my data table I have month by month listed started on 01/01/2014 ending 12/01/2014 but as soon as the month is no longer January the last column in my budget cannot find the information needed due to it looking for 2015. so what I would like to know is if there is a way to make the data table change the year to the following year after today is beyond that month, so for example on March 1 2014 both January and February would be changed to 2015.
Attached is an example : Budget Example.xlsx‎
I have dates in my column “A”, for example (A1 cell =22-Mar-1971), (A2 cell=30-Dec-1965). Now my requirement is in B column date and month from A column and year should take current year. Output in B column (B1 cell =22-Mar-2009), (B2 cell=30-Dec-2009)
View 3 Replies View RelatedI am trying to do an IF statement, if the activecell's value is equal to this current year, do nothing and if its not the current year, to offset one column to the right and insert a blank column, then copy the whole column to the left and replace it with the new blank column.
View 1 Replies View Related