I have developed a financial calculator that asks the user for the "input date" which is used to record balances as of the input date. My interest calculation for the first year is based on the current date compared to the input date. For example, if the user is keying in a current balance of 10,000 @ 10% interest, and the "statement date" or "input date" is 12/30/2013, and the current date is today, 2/4/2014, then it should calculate interest for the entire year of 2014. It is not doing that. It calculates $3 interest.
But if input date is 6/30/2013 with current date of 2/4/2014, it seems to work OK. It calculates interest of $504 in that case. It appears to get messed up with the year transition between current date and input date. The formulas I have listed below appear to work fine except when the input date is 2013 for the year and the current date is 2014. The formula does not "see" that input date was last year. There must be a minor tweak to formula I am overlooking.
Cell C2 = Today's Date=TODAY()
Cell C3 = Input date (user keys in date in mm/dd/yyyy format)
Cell E2 = "translate input date to year format" =DATE(YEAR(C3),12,31)-C3
Cell E3 = Investment Rate
Cell G2 = yr 1 interest rate adjusted =(E3/1)*($E$2/365)
Cell C21 = Current Balance
Cell D21 = Interest Yr1 = C21*G2
I need the interest calculation to account for partial year accrual.
I use Excel 2007 , I created Interest calculator , on Daily basis , to caluculate interest , compounded quarterly.
But I want to make it compact , as d one I created is long enough.
A3 = Principal Amount B3 = Date of Investment C3 = Interest as on Date D3 = Number of Days , amount Invested {comes out of formula set} E3 = Rate of Interest
Now in F3 I want the Interest amount , compunded quarterly.
Some times NUMBER OF INVESTED CAN BE LESS THAN 90 DAYS TOO...then what ?
I have a column of years and a column of numbers representing annual amounts placed in a savings account for the year. I would like to calculate the balance of interest earned added to the balance of the account and then calculate the interest earned on the accumulating amounts each year. The results would be the account balance displayed in an adjoining column. So far, I have not found a worksheet function for that. Could someone point me in the right direction? Perhaps there should be several columns of data?
If I invested $350 per hectare into a project and at the end of 12 years that investment yielded me $150,000, what would my rate of return be over the life of the project?
I've uploaded my spreadsheet. I asked my professor if our formula had to take into account whether or not our payment would be changing. As long as I calculated the initial amount - which worked out to be around 30 dollars, I could assume I paid that amount for the entirety.
ASSIGNMENT 4
My latest Mastercard statement shows a balance due of $1,696.96.
Based on recent bills, assume that the minimum payment amount is computed as follows
(balance_owed)*(annual_interest_rate/12) * 2, then rounded to the nearest whole dollar amount.
The minimum payment amount is the amount calculated above or $25.00, whichever is the greater amount.
The annual interest rate for purchases is 11.24%. If I make no more purchases and always pay my bill on time how many years and months will it take be pay it off at the current minimum payment amount each month?
How much total interest would I pay if I repay the loan this way?
What fixed monthly payment amount would pay off the debt in 2 years?
Assume monthly compounding of interest. All calculations are to be done in the Excel worksheet.
The functions NPER() and PMT() will be useful. Note that either the Present Value or Payment amount should be negative (not both) because of conventions concerning the direction of cash flow."
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 create this spreadsheet as a loan schedule using average daily balance method. (1/payment is constant, fortnightly 2/interest is 5.5% per annum)
In the interest column, at the beginning of each month ( when the day is 1) the interest will be added up from calculation of previous month daily balance.
My idea is that at interest column(let start at 1/08/2013) if (day(A49)=1, average the 30 or 31 cells above E49, 0). I will manually make adjustment for February where 28 or 29 days applicable.
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
My Financial year runs from Oct to Sept. My current month is June 2008 and the YTD Expenses is in Cell P2032. I have set up a formula to calculate the prior YTD Expenses as sum(AU2032:AM2032) This is for the period Oct 2006 to June 2007. When I do July 2008, I would like to the formula to caculuate the prior YTD Exp as sum(AU2032:AN2032)
How can I get the formula to Change Automatically based on the Month & Year as per the example shown above?
I need to calculate SUM and AVERAGE of rainfall for each and every year separately and must be displayed separately in a separate column. For your easy understanding, I have done manually and attached the excel sheet.
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 have a spreadsheet that tracks “points” for hourly associates on a daily basis that totals weekly and monthly. This spreadsheet works on a rolling calendar year instead of a fiscal and I need to be able to capture the totals for periods of time by days.
For example, I would need to view how many points ‘employee x’ has from 2/25/09 through 2/25/10. I have attached the spreadsheet, which includes tabs for each week ending and a summary page.
I need a formula that will calculate the number of days (ex from 10/1/08-1/1/12) and will differentiate between leap year and a regular year. I am trying to calculate interest between 2 dates and leap year is calculated on a different interest rate. Right now I have it set up so I have to manually type in the leap years in a separate field instead of being able to just you point a to point b.
I have a person who was paid £1000 pension pa for ten years. I've found out that the pension should have been split 50/50 with half increasing by 5% pa. Thus in year 1 total pension would be £1000. In year 2 the total pension would be £1025 ((£500 x 1.05) plus £500)
I know that in year 10 the total pension should be £500 (non increasing) plus £814 (£500 x 1.05 to the power of ten). My problem is how do I work out a formula which calculates the total arrears due in year ten? I'm thinking the arrears due after the ten years is £314 but something is telling me it's a lot more.
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.
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?
I am currently trying to create an excel spreadsheet where I would know how much interest I will pay in the next six months, since the balance changes every month I am not sure how to formulate the total interest paid with in a specified time period.
I currently can calculate the interest and fill in the below rows with each month but I would like to simplify the report so that I can add the Balance, Rate, Payment(2% of balance), and Time period and in another cell it will tell me how much interest I will pay in that time period. Bascially I would like to keep all the calculations in one row.
For example
Balance | Rate | Payment | Time Period | Interest Paid $1,000 | 10.00%|$20.00 | 6 | $48.56
I am trying to calculate the effective annual interest rate earned on an investment and find the results are close but not really accurate. I suspect because I have not included the frequency of interest in my existing formula
r = n * nt root (A/P-1)
where; r = the effective interest rate n = the number of times interest is added per year t = the total number of years A = the current value P = the original value
The 2 problems I face are; 1. Confirming this formula would provide the correct answer (need maths expert here) & 2. How would "nt root" (as in sqr root, but using the product of the years and frequency) be used in Excel
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.
I need to find the last row of a table I'm manipulating with VBA. The last row contains only the subtotal of a specific column. I am currently using the following code to find the last row. (NB:'j' is the the specific column, the formula in the last cell is shown in the Do While conditional, but the value 109 can change)
Code: Do While Cells(i, j).Formula "=SUBTOTAL(109,[BID PRICE '#1])" i = i + 1 Loop
However, this code will not work if the user adds a row to the table. I've tried using a partial string search, but the do while conditional is always true. I assume this is because the cell contents are a formula and not a string.
Can I change my conditional to search for just the "=SUBTOTAL" portion of the formula?
I'm working on a formula to make it enable a part of the text then return the best possible match. Below is my formula
=MATCH("*"&$A11&"*",'[Customer Master List - 05.30.xlsx]Export Worksheet'!$B$82:$B$1298,0)
However, it works with some text but won't work for some.
For example, I have this text CARE-A-LOT, INC and in the master sheet there is a similar text like this CARE-A-LOT. I want it to return CARE-A-LOT as this the best match possible.
I'm trying to figure out a formula to be able to look at a column of txt and if it finds the word total it need to output the number at the column next to it. If the word total isnt in the text then it should leave it blanks (see below). Basically I want a column that pulls only the totals amounts in the column.
Aaron Drielick 3 Aaron Drielick 2.5 Aaron Drielick 37.5 Aaron Drielick Total 151.0 Akila Subagaru 31 Akila Subagaru 1.5 Akila Subagaru 1 Akila Subagaru 1.5 Akila Subagaru Total35.0 Albert Major 4 Albert Major 6.5 Albert Major 2 Albert Major 19.5
This is for Excel 2007,I have two sets of model numbers. One set is the full model numbers of the units we use, and the other is an abbreviated form used to lookup up certificate numbers. I need a way to match these up so I can use one set of search criteria to find out if there is a match. Here is an example of what I need to match with a partial text match:
H,AE35(6,9)36+TD and AE3563636D145C2501AP H,RE36(6,9)36 and RE36936C145B2505AP
if I could do this with a formula that matches multiple items at the same time (ie; if A & B & C match=true) with the above model number being one of those items (certificate numbers are issued for sets, but the other model numbers are fine).If that's not possible, a one time VBA run to match all of the abbreviations at once would also work. If these items are matched up with a one time VBA, the VBA needs to account for their being more than one match for each abbreviation depending on the size of the unit.
So H,AE35(6,9)36+TD could be matched to: AE3563636D145C2501AP AE3563636D175C2501AP AE3563636D210C2501AP
Trying to do a year to date formula with my numbers going across with actual,goal, percent to goal.
I want to have the ytd auto sum based on my As of date that I did a list validation for. Can't get it to work and getting very frustrated - can anyone held with this? I attached a worksheet with the YTD listed on the far right.
