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] ........
I have 4 Loans of various interest rates, balances, and minimum payments.
Assuming I have a certain amount of money to pay out each month, how can I minimize the total amount I pay over the lifetime of the loans?
Given:
Total Monthly payment: M
Interest Rate for each loan: R1, R2, R3, R4
Initial Principal for each Loan: P1, P2, P3, P4
Minimum Monthly Payment: Min1, Min2, Min3, Min4
Each month, how should I distribute M over the 4 loans?
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
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?
I have used the PMT function but this gives me the total to pay per month.
I want to know what the repayments would be and also allow me to add in each one of the periods and extra repayment option.
So if in the year I wanted to pay an extra $100 per month, I would put $100 next to each period as in a particular period(s) I might not have to put in there but want to predict what the amount owing on the house is.
Is this possible or is this too complicated.
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
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.
I need to calculate the monthly repayments on a loan taken out over a certain amount of years, which I can do fine.
I just cant get my head around how to calculate monthly repayments over a certain amount of years when the intrest is compounding annualy.
What I have so far:
p*(1+(r/100))^n
Where p is value of original loan, r is annual intrest rate, n is amount of years, and I am hoping I am right in saying this is the total repayable amount of the loan?
Then putting that aside I created a amortization table. (which I am certain i forgot to include compound intrest in!)
To keep it short i followed this guide for the amortization table.
and now I am so confused about if I should be using PMT, PPMT, NPER?!
I have a problem here in calculating the Daily sales target based on Monthly Targets and Year End Target.
I am attaching the file herewith which has Yearly & Monthly Targets defined. Need calculating Daily targets which should match with Monthly & Year end target .
I have the split of day wise sales for a week as well in another tab. However not able to get the exact monthly target as listed .
I 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 keep coming across bonds having different annual interest rates and different compounding frequencies (quarterly, half yearly and yearly).
I know there is a YIELD function, but it requires so many inputs. I was wondering whether we can calculate cumulative yields just from annual interest rates, compounding frequency and investment duration?
I have a table of yearly totals for the amount spent by x. I also have a growth rate for each month so for example in 2001 in jan the growth rate might have been 0.3% and feb 0.5% What I want to do is for each month based on the growth rate and the total produce a value for each month which sum to the total amount. It's also important to note that it restarts each year.
Link for excel file is here: [URL] ...........
if u check my file u can see some comments in calender sheet in january but i want to apply this for other months by repayment dates..
View 11 Replies View RelatedI have the following rows in my excel sheet:
-Cash needed to start the quarter.
-Net cash position.
-Begining of the quarter debt owed.
-Internal repayment.
I am trying to develop a formula for internal repayment which must fulfill following requirements(Net cash position-cash needed to start the quarter),the remaining amount goes to internal repayment but that must be <=Begining of the quarter debt owed.
I have a table with
Column A - Suppliers
Columns B to M products with a product group (in row 1)
Prod Group..Core.......Core........Outer........Inner.........Core
Supplier......Type A......Type B.......Type A........Type B.......Type C
AB Ltd........1000.........2000..........500.............750...........5000
CD Ltd........3000.........5000..........100.............950...........8000
AB Ltd........2000.........4000..........600..............800..........7000
I would like to know how to sumif when for eg supplier is "AB Ltd" and the product type is "Core" in this eg = 21,000 (how to paste a table)
I have a spreadsheet that I am trying to create a formula for that will bring back the data found when you compare an X and Y axis. A sample is attached as the data is huge and I figured what ever you all created I could modify.
I need it to bring back the data found when I run my finger down the column till I hit the appropriate row.
I have a list of jobs over a 24-hour period, that looks like this:
job1: 03:00am, duration 10 minutes
job2: 04:00am, duration 20 minutes
job3: 09:00am, duration 04 minutes
job4: 01:00pm, duration 65 minutes
Now I want to make a horizontal bar, that divides the bar into a 24-hour period (e.g. gray background) and fills the gaps that the system is in use with green parts, so in this example, the whole bar is gray and the part from 03:00am-03:10am+04:00am-04:20am+09:00am-09:04am + 01:00pm-02:05pm is filled in with green.
I have a list with about 300 of those jobs, so it would be nice if I could automate this. How to do this in Excel/VBA ?
I have chart like below. In empty cells I want either 1 or 0 (1 if software is installed and 0 if not).
Excel
Outlook
Powerpoint
Word
Computer1
Computer2
Computer3
Computer4
Computer5
Data of computers and their software are like this:
Computer1
Word
Computer1
Excel
Computer1
Powerpoint
Computer1
Outlook
Computer2
Outlook
Computer2
Excel
Computer3
Outlook
Computer4
Outlook
Computer4
Excel
Computer4
Word
Computer5
Outlook
So called Matrix Lookup was very close, but it finds data FROM Matrix (aka that first table). Is it possible at all?
Excel and Windows version:
Excel 2010 SP1
Windows 7
I want to achieve is a procedure that inserts horizontal page breaks at certain parts of the sheet where there is a cell equal to 2. Here is the code I have so far.
Sub insert_pagebreak()
Dim printbreak_cell As Range
Dim j As Long
Dim i As Long
ActiveSheet.ResetAllPageBreaks
Set printbreak_cell = Range("AD1")
j = 1
For i = 1 To 100
If printbreak_cell.Value = 2 Then
Set ActiveSheet.HPageBreaks(j).Location = printbreak_cell
j = j + 1
End If
Set printbreak_cell = printbreak_cell.Offset(1, 0)
Next i
End Sub
Everything works until the cell value reaches a 2, and then once it goes into the If statement I get a 'Application-defined or object-defined error' at the below line.
Set ActiveSheet.HPageBreaks(j).Location = printbreak_cell.............
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.
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).
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
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
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?
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 RelatedI 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 ...
how to add drop list box or combo box in this yearly time sheet so every employee has his own record in this time sheet so when ever i select name from drop list all info changed, i did include table in sheet 1 as an example.
View 4 Replies View RelatedI am trying to get the formula for calculating yearly rental imcome. The range is 10 years and the interest is 34%. The first year payment is 42,000 and the 10th year payment is 56,000. I can't figure out how to do the other years. The principal is 325,000 and the sale value is 425,000.
View 4 Replies View Relatedi have a problem that i have been trying to get over for about a week now.
i need to calculate a lease commission, with an extensive amount of variables.
first i need to find the length of the total term which should be anywhere between 1 to 10 years.
then on a annual basis i need to define how many months are billable in that year.
which gives me to variables to account for there, which are
A= initial free months, non paying
B = the last month of last year may only be a half year
i think i have worked that out pretty successfully, so next i need to calculate the rent for each year period. with several variables
a= the rent can be caculated :
-by per month basis
- by annual basis
- by a per square foot basis
b= next in relation to annual rent operating expenses may also be calculated in the annual rent number also by the same variables, however it may or not be calcuated into the number depending on the lease.
c. this is where i am at now, and its killing me. i need to account for rent adjustments for each year.
rent adjustments can start from either the lease start date or the date that rent starts which would be after the lease start if free rent is granted.
then the adjustments will continue through the end of the term and be implimented every x number of months.
the value of the adjustments will either be a percentage of the first years rent usually 3-5 %
or per sf, per month, or just flat rate per year. but it will escalate each year.
for example year 5 is x amount of ajustment from year 4.
i am finding difficulty in finding an annual value of the original lease term in relation to this date series. expecially if the adjustment periods leave a remainder carring over to the next year, or if their are several adjustments in one given year.
any help would be appriciated on this.... i know its pretty complicated, and i have rewritten this code about 30 different ways , i am at a loss right now.
if you think you may want to see my file let me know and i can post it
1. Want to findout in which stage the loan is ? Eg: 1212321231 is in PROCESSING STAGE because the date appears under processing .
2. want to find out how many stages it has paased ? Eg: Loan 1212321231 has passed 3 stages and now in 4th stage(processing).