Some years ago I came across a formulae to calculate Daily Interest on a Building Society Savings account in the UK. I have used this since but find my calculations never work out the same as my BS, although to my advantage! It is =B3*B4/360*DAYS360(B5,B6,TRUE) Where:
B3=Capital B4=Interest Rate B5=Starting Date B6=Finishing Date
For some reason the formulae uses 360/year and not 365/year. Using both still gives wrong answer.
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 am looking to calculate compound interest over a period of 10 years.
I am looking at putting a lump sum in at the beginning and contribute in monthly installments for the entire 10 years. How would I go about this.. I know there are formulas there, however I'm not a financial person at all..
I'm trying to come up with a way of calculating Money earned over time by compounding interest, but with a twist. After reaching a set amount of money, all money above and beyond does not gain interest. example:
Principal: $120 (user input value) Duration: 9 (user input value in days, compounding daily) %Intertest: 4% (user selected value, either 2% or 4%) Max interest you can earn: $6 (fixed) Max interest generating money: $150 (variable dependant on %interest, = $150 or $300) Response/Answer is final value. I don't need the daily results like the example. Result would be: $170.84 $124.80 (4.80 interest) $129.79 (4.99) $134.98 (5.19) $140.38 (5.40) $146.00 (5.62) $151.84 (5.84) $157.84 (6.00 reached the max interest level) $163.84 (6.00) $170.84 (6.00)
my equations I have so far only do one (below 150 total) or the other (above) but not both. and its just a regular formula: =IF(P<M,IF(P*I^D<M+1,P*I^D,"over limit"),P+6*D)........................
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.
The way I have this sheet setup is to calculate a 20% fee off the interest earned column "D". Say you earned 6.5% on a beginning balance so the interest earned is in col "D". This works fine for a 20% fee but I need the fee to calculate a 10% fee if the interest in column "C" hit 4% or below and it also needs to be able to calculate a 20% fee if the interested earned is above 4%.
I have a spreadsheet that has columns of monthly values for three years of financial data and where the values for the latest month are added to the last column. Months that have not been completed will have a zero value (e.g. Jul-09).
I'm looking for a way to calculate (name) a fiscal month based on a date range. Example attached. Columns A & B are a date range and column D is the desired outcome. I'd prefer a formula solution, but am open to anything as I haven't a clue how to begin with this.
I'm working on a Calendar. One where all the user does is input the year, and the rest of the Calendar fills itself out, as to the days.
Leap year is causing a small problem. There may be an easier way to do this (actually, I'm sure there is, but anyway), is there a way for a cell to automatically figure the last day of the month?
IE: I put "2007" in a field. Another cell auto matically reads as "28" (last day of Feb for this year). Subsequently, when I enter "2008", the same field reads "29".
The rest I think I got ok, but everytime I get a leap year, it shoves all my formulas down a cell, thanks to the extra day, and they're all off by one (February calendar showing last day as "01" from March, and "29" as the first day for March, from Feb).
I am trying to construct a schedule of tasks to be done by certain dates. If the last day of the month is entered (say 11/31/09) in a cell, is it possible to find out the date for the third Thursday of November or the first Wednesday or the last Friday?
I am attempting to create a formula that will select a value from a lookup table where that value is related to a specific date BUT that date, derived from an input date (any day within a selected month) must always be the first BUSINESS day of that month.
I have a workbook with two worksheets in it.
The first worksheet is the input page.
On that page I want to have a list of dates which display the FIRST BUSINESS DAY of a month - e.g.
Cell A1 - Date entered as 19/9/12 and displayed as 19-Sep-12 Cell B1 - NEEDS THE REQUIRED FORMUAL TO CALCULATE THE FIRST BUSINESS DAY OF THIS MONTH (SEP) (in this example, this should be 3) and displayed as 03-Sep-12 Cell C1 - Will display the value from the lookup table on worksheet 2 (see below) related to the date in B1
The second worksheet is a lookup table containing a sequential list of dates from 28-Jan-69 through to present (19-Sep-12) in Column A.
The adjacent column B contains the related value to that date (the value being a foreign exchange rate) - e.g.
Daily Date USD
29 June 2012 1.0191
1 August 2012 1.0507
Worksheet 1, Cell C1 should display the value of 1.0266
Ive created an invoice. The data is being entered on the previous sheet & then this invoice reads the data from the previous sheet through the formulas as shown below. The data that is entered, is entered contineusly everyday, so the invoice calculates the amounts from the entire data input sheet. I need to add some controll to the invoice where I can select a starting date & an ending date, and then the amounts must be calculated on the invoice according to the selected dates.
H I J 27 Qauntity: Cost: Amount: 28 55 R 185.00 R 10,175 29 30 Net amount: R 10,175 31 Vat: R 1,424.50 32 Amount Due: R 11,599.50
Invoice [Table-It] version 09 by Erik Van Geit
RANGE FORMULA (1st cell) H28 =SUM(Divenic!G5:G5004) J28 =SUM(H28*I28) J30 =J28 J31 =J30*0.14 J32 =J30+J31
I am using a formula to calculate the last day of the month, using any date of the month in a worksheet in cell A13, this cell is also linked to another worksheet to pick up a date, using the ISBLANK function to prevent a dummy date entry appearing if the field in the linked ASHBY RISE worksheet is blank =IF(ISBLANK('ASHBY RISE'!$C$5),"",'ASHBY RISE'!$C$5)
The last day of the month function is shown below =DATE(YEAR(A13),MONTH(A13)+1,0)
This works fine if there is a date in A13, but returns a #VALUE! error if cell A13 is blank. I have tried using the ISBLANK function, but I am still getting the #VALUE! error. Of course I may have the sysntax incorrect.