Formula That Calculates The Total Rent Amount For Particular Dates
Oct 5, 2009
I need a formula that calculates the total rent amount for particular dates. Example would be a formula that includes a date starting at the first of the month (October 1, 2009 to September 30, 2010) and also would include a date starting at a date later than the first (October 3, 2009 to September 30, 2010). The figures needed would be the monthly rent (ex: $1,000) and a pro rated rent amout for a lease that starts after the first of the month. The pro rated amount would also have to include the years with a leap year. I have been working on this but I know there has to be an easier way.
Column C contains zip codes. Column D contains # of adults in the household. Column E contains # of children in the household.
How might I create a formula that calculates a total # of adults AND children in Columns D and E based upon a series of specific zip codes (but not all the zip codes) in Column C?
i 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
I have 2 tables, one with invoices, the other with purchase orders. I would like to have a "PO Amount Remaining" column on the invoice table that looks up the PO listed on the invoice with the PO $ amount on the PO table. Once it is matched, I would like to subtract the total invoices to give me the amount of the PO that is left. Basically I would like the "PO Amount Remaining" column right now to have $4,200 listed in each row for invoices 1 & 2.....
I am trying to set up a spreadsheet which automatically calculates the start and end dates of project tasks, by looking at the order in which tasks need to be completed. I have attached a spreadsheet to show what I am trying to do.
set a formula to auto calculate the staggered rent for the month. When I change the date, it will tell me for this month I should charge according to the rates for the year.
Rent for the month Start Date Year 1 Year 2 Year 3 01/07/14 Explanation 01/08/13 10 20 30 10 < 1 yr = 10 01/07/13 40 50 60 50 enter 2nd yr = 50 16/07/13 70 80 90 76.29 (15/31*70)+(16/31*80) 16/07/13 10 20 30 15.16 (15/31*10)+(16/31*20)
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 need a formula to calculate the sum of a range of rows.
Example:
If rows 1-10 have a width of 12.75 each, and rows 11-15 have a width of 15.5 each. I need a formula to tell me that the sum of the widths of rows 1 to 15 is 205.
I am trying to write a calc for a total an amount based on a list choice Here is the Set up Fields A1 thru A255 are number 1 to 255 Fields B1 thru B255 are number 255 to 1 Field C1 is a List box set using A1 to A255 and its list Field D1 is set as the needed Calculation
When you choose a number in C1 the D1 calculation should total the amount of Fields B* to B1, * being the corrisponding B field to the A field from the list
Example C1=100 (Field A100) D1=Sum of Fields B100 to B1
I have pivot tables where I need to look through a series of names and sum the hours and dollars charged by that person. When I try to do a pivot I can get it to read
column 1 Column 2 John Doe $4000 Mary Smith $ 500
But if I try to add the hour columns it breaks it out by the week the hours was charged
column 1 Column 2 Column 3 John Doe $2000 4 2000 4 Mary Smith $ 250 5 250 5
I want it to just give me one dollar total and one hour total per employee.
I'm trying to make a worksheet that calculates monthly residuals. For example, if I have income of $275 in the first month and then the next month I have the first $275 and an additional $275, and so on. I've created the following formula
where RC[-1] is the base amount that adds on every month. This gives me the number I am looking for. However, I am wondering if there is an easier way to do this.
Excel 2003 SP2 -------------- I have been programmatically setting the formulae in a worksheet. I have various formulae:
Example 1 - =COUNTIF(Data!M$6:M$9871,3)/$E$4 The cell is formatted Percentage, 2 decimal places. Using the formula evaluator the value returned is 56.80% The cell displays 56.80%, all is good!
Example 2 - =SUM(IF(Data!$J$6:$J$9871=$D8,IF(Data!$M$6:$M$9871=3,1,0)))/$E$8 The cell is formatted Percentage, 2 decimal places. Using the formula evaluator the value returned is 53.37% The cell displays 0.00% !?!
using Office Professional Plus (2013). Within my spreadsheet, I have a series of progressive dates to be used in as a timeline. Ultimately, what I need to accomplish is that when I change one date, all the proceeding entries update according to my timeline criteria (in this case 3 weeks or 21 days). I created a SUM formula that worked but only calculated one time. Once the calculation completes, the cell drops the formula. I need this to be constant as the dates often change. How do I get the formula to maintain? Further more, is there a better method to accomplish the task at hand other than the SUM option?
I am having an issue creating a formula that calculates % of Budget based on signs. What I mean is positive budget, negative actual and vice versa. Here are all the cases. Can someone please create a formula that I can copy down.
Actual Plan% Budget 85,60696,85188.4% 70-82185.9% -1,530-3,786247% -100100-200%
to run a macro that enters a new row after each set of currencies (which have been sorted). I now need to enter a total balance cell within this empty row which calculates the total amount for each currency.
I need to do this using the SUMIF function below: SUMIF($J$3:$J$300,"L/C",$I$3:$I$300)-SUMIF($J$3:$J$300,"L/D",$I$3:$I$300)
the above basically lets me calculate the total but as no - and + signs were input it does this by recognising the 'dr' and 'cr' in the column next to the amount.
Is there a function, or how would I write a vba to figure out the following.
I have 86 items all with a different price, which come to a total of $348,359.33, is there a way to figure out which combination of the 86 entries will give me a total of $57,673.36
Is there any formula that could calculate total average number of recruitment days (i'ts one number), but only for "Diana" and "Skipper" (excluding "Jennifer")?
I have a report that I want the total amount (in the bottom right corner, but can vary in row number) to appear in a particular cell in the header. In the macro, I selected the cell & entered "=CTRL+End". It works in reality, but not in a macro. It returns a visual of 0.00, but "=XFB1048565" in the formular bar. How can I get the header cell to return the correct total amount?
I need to prorate a total dollar amount by a selected number of attendees. How can I do that with code, I know the formula for excel, but how do I put that on my user form so it only does the math as a new entry is submitted? I do not want to have the formula on the spreadsheet which is my report unless there is an entry on the row, which will only be the case when a user completes the user form. I just do not want the div/# error on the sheet visible if at all possible.
I have an excel spreadsheet which calculates the count and value of line items based on a report. I use the sumproduct function to calculate this as it involves multiple criteria. The formula is use is
I think the possible error is coming out of this criteria (LEFT('Report 50'!$S$2:$S$64992,14)="credit transfe")
Out of one particular field i am picking those line items in which the first 14 characters are "Credit Transfe", if i eliminate this criteria i dont get error.
But this is the main criteria for my calculation. Most of the time i dont get any error , but sometines i get the # Name Error? When i double click on the cell it goes to one column. I dont know what to do from there.
I have included my budget so helpers can see it. I am trying creating a formula on mini-dashboard on A8 (Under the total budgeted amount) that will check the total budgeted amount based on the values I enter on the categories sheet. Of course it will be controlled using the drop down menu on A2 on mini-dashboard. I thought I had this working with a sumif function but it seems to have quit working.
I have a spreadsheet used for calculating information based on the dates specific shifts are requested/cancelled by our clients. I have a formula for working out if a date & time of cancellation is less than 48hrs notice of the shift starting. This is because we have cancellation fees based on this.
What I have is this formula: =IF(A16="","",IF(INT(A16)-INT(G16)<2,1,"")) that returns a 1 if that shift is cancelled within 48hrs notice. This works fine but I have to now change the notice periods to the following:
edit this formula to take this into account? I figure it's using multiple IF's and changing the <2 into something else like the number of hours but I'm not sure of the exact syntax.
I have a report which has a list of customers, each customer has 24 columns which represent the payment history over 24 months. If a payment has been made for that month the date and time (formatted correctly) will be populated in this cell.
Each customer has a product name attached to it so a product can appear several times. I need is a formula that shows the total payments recieved for a particular month for a particular product. For example.
I have managed to create the following flag which works a treat, it picks up a date an account was set up but looks at 1 column.
number for item detail level from category standpoint. For example, in the category sheet has total number per category and per month and in the item detail sheet has a list of item number with category. I want a formula to recognize which item belongs to which category then use the total number of category by month to multiply with % of total in column D from Item Detail Spreadsheet.
I have attached the spreadsheet in a pdf. I can e-mail it to anyone who is is willing to help me.
I am trying to calculate the amount of income per calendar year from each tenant.
I have, for each tenant, a commencement date (the date that the first lease year began) and a termination date (the date the last lease year ended). If a tenant has signed a 10 year lease, for example, and his lease commenced on February 15, 2005, then it will terminate on February 14, 2015. The 10 years in that range each have a different rent for that year. Year 1, (from February 15, 2005-February 14, 2006) he will pay $4k a month. In Year 2, (from February 15, 2005-February 14, 2006) he will pay $6k a month. What I want is a set of columns that show the total rent he will pay in 2005, 2006, and so on. The 2005 total should be: $42,000 (10.5*$4000). In 2006, it should be: $69,000 (1.5*4000 + 10.5*6000), and so on to year 10 (being split between 2014 and 2015. Also, please note that the lease could commence on any day of the month and they pay the corresponding pro-rata share of rent based on the number of days into the month the Lease Year is starting/changing.
The result should look like this:
[2005][2006] [$42000][$69000] next tenant amounts next tenant amounts
Is it possible to have my lease rents be automatically adjusted on the anniversary of their commencement date using the current system date? Maybe, this would work using the excel TODAY function. Excel would need to compare the current date with the commencement date and calculate how many years have since passed. It would need to use a compound interest formula to calculate what the present rate is.
In the document attached, you will find (lease commenced, original rent, escalation rate and present rent. There are also ammendments which follow the present rent which show how rent has increased due to new lease agreements or amendments. The most recent rent to use is present rent, but it would be great if excel could show how the rent increased or changed since the lease start.
The document is the following: Basically, these are multi-year property rentals which on the anniversary of the contract signing increase by 4% or 5% annually (or whatever the escalation is). That means, if I rent it out on March 1st 2007 for 1000$, it will be $1050 from March 1st 2008 and then 5% increase on every anniversary. It works just like compound interest over given years.
What I want to know is it possible to have Excel update my spreadsheet depending on the date automatically. Is there any kind of Macro program? I have over 600 rents, that is why I want the process automated. I would want the rents to increase on the anniversary of the contract signing every year and so basically the Excel would update itself, or it would update once I turn it on and then refresh it.
Next question I have is, say I scroll my mouse over the rent column. I look at building A and look at the rent for this year. Is it possible that once I pop my mouse over it a small chart can pop up and describe the rents for the previous years. Meaning a chart that would quickly pop up and show the 5% decrease every year for the past say 5 years.
