We’re rewarding the question askers & reputations are being recalculated! Read more.

# Tag Info

35

First, calculating interest on your bank account daily makes the most sense because your balance in a bank account typically fluctuates throughout the month: that is, you make deposits, and you make withdrawals. If the bank calculated interest only at the end of the month, say, based on your balance at that point in time, then it might not be fair to either ...

18

You just use the compound interest formula: Principle * (1 + Rate / Time) ^ Time For Cell C2 you want this formula: =B2*(((1+(D\$1/360))^(C\$1-\$A2))-1) Column A is deposit date Column B is deposit amount Cell C1 is today's date Cell D1 is the annual interest rate Most savings accounts that I know of compound interest daily and credit earned interest ...

15

I would say that all of the reasons you list in your question are valid, and I would add the following... You are in the landscaping business, not the accounting business. If you manage everything in spreadsheets, at least one of you has to become the bookkeeper and leave the landscaping to the others. Spreadsheets are "agnostic" in how you use them, so you ...

8

The "Future Value" function does this. =FV(rate, number_of_periods, payment_amount, present_value, [end_or_beginning]) For example: =FV(2%, 12, -100, -400, 0) Note that the payment_amount and present_value should both be entered as negative numbers, otherwise it outputs a negative value See the Google support article for more info and related functions.

7

Bren's comment is right on the mark. The typical solution is to divide all bills by 5, and for special items, the person buying it just marks his name that it's not community food. Your attempt at a granularity level this detailed is admirable, but produces false results. What happens when I claim to be a zero percent milk drinker but when someone gives me ...

7

Set your xirr formula to a very tall column, leaving lots of empty rows for future additions. In column C, instead of hardcoding the value, use a formula that tests if it's the current bottom entry, like this: =IF(ISBLANK(A7),-C6, C6) If the next row has no date entered (yet), then this is the latest value, and make it negative. Now, to digress a bit, ...

7

http://www.tvmcalcs.com/calculators/excel_tvm_functions/excel_tvm_functions_page1 The link provided above shows you how to turn Excel into a Financial Calculator. Your question is a little more advanced because of the specific dates involved.. but If I simplified the 1st question and used the FV function in Excel this is what i would do: What to include ...

7

0.250% of what? Because \$100 x 0.25% = \$25. How am I getting \$0.02 a month? You're confusing "25%" (twenty-five percent) with "0.25%" (one quarter of one percent). Your credit union is making interest payments twelve times per year. Your yearly interest rate is 0.25%. Divide that by 12 to give the monthly interest rate: 0.0208333%. So, if you have \$100 in ...

6

Use the round function. If the value of 1.085 is in cell A1 =ROUND(A1,2) The 2 tells Excel how many digits to the right of the decimal point to keep. You can also use zero or a negative number. Excel has other functions to control rounding including ROUNDUP() , ROUNDDOWN(), and MROUND()

5

I think your reasons are good. Fundamentally accounting software is built to ensure you record your accounting data effectively with minimal mistakes and good auditing. But you still need to use the tool properly to get the benefit. One other advantage is that many accountants are familiar with, say QuickBooks, and can do your accounts more effectively if ...

5

For a personal finance forum, this is too complicated for sustained use and you should find a simpler solution. For a mathematical exercise, you are missing information required to do the split fairly. You have to know who overlaps and when to know how to do the splits. For an extreme example, take your dates given: Considering 100 days of calculation ...

5

The MIRR formula uses the finance rate to discount negative cash flows, but since the only negative cash flow in the example in in the current period, there's nothing to discount. It's meant to solve problems with IRR like when there are both positive and negative cash flows, which can result in multiple answers for IRR. The example they give isn't a good ...

4

An improved approximation using your growth rate assumption would be: value at year end = (value at year start * 1.05) + (total monthly deposits * 1.025) i.e. apply the full growth rate (5%) only to the beginning-of-year balance, and, apply half of the growth rate (2.5%) to the total monthly deposits made in the year. Why half? Consider: If each monthly ...

4

When I was in grad school (at an engineering school) my apartment-mates and I came up with this formula: We each bought 100% of the food we intended to consume. We each consumed 0% of the food that we did not buy ourselves. Worked marvelously.

4

The solution uses the PMT function which has the syntax: PMT(rate, nper, pv, [fv], [type]) where Fv is Optional: The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0. and Type is Optional: The number 0 (zero) or 1 and indicates when ...

3

The formula for determining the number of payments (months) you'll need to make on your loan is: where i=monthly interest rate (annual rate / 12), A=loan amount (principal), and P=monthly payment. To determine the total interest that you will pay, you can use the following formula: where P=monthly payment, N=number of payments (from above formula), and A=...

3

This is the internal rate of return (IRR) calculation for the second case. There are 731 days from 2016-02-16 to 2018-02-16. f2 = -13660 - 20000/(1 + r) - 19900/(1 + r)^(700/731) - 19880/(1 + r)^(669/731) - 19300/(1 + r)^(639/731) - 19200/(1 + r)^(608/731) - 19200/(1 + r)^(34/43) - 19100/(1 + r)^(547/731) - 18630/(1 + r)^(12/17) - 18450/(1 + r)^(486/731) - ...

3

I believe the 2.63% is the rate. That's what: =RATE(20,-1238,0,32880,1) gives, as does: =XIRR(R2C2:R22C2,R2C1:R22C1) (with 1238 in R2C2:R21C2, -32880 in R22C2, 1/1/95 through 1/1/14 in R2C1:R21C1, and 12/31/14 in R22C2) and if you check that by "hand", that is in some column, in rows 2-21, put: =POWER(1.0263,20-ROW()+2)*1238 then sum those, you get \$32,...

3

I would add to your reasons: QuickBooks makes it easy to send statements or invoices to customers that request them QuickBooks has a number of built in reports that may give you insight into how your business is doing (revenue month by month through the year, putting customers into categories that reflect how you landed them and then comparing the ...

3

So your whole approach, and the attempt to scale this is flawed. You will alienate roomates, provoke arguments, and make everyone's life more difficult. There are too many variables and unforeseen possibilities. For instance: "Why should I have to pay for Joe to go buy the expensive organic milk when I'm fine with the cheap stuff?" "I planned on being ...

3

Keep in mind the number of months or years before you break even. You pay money to lower the interest rate, and lower the monthly cost. But it takes a number of months, using your numbers \$7,000 to save \$160 a month will take ~43 months. That is before figuring in the future or present value. If you sell or refinance the mortgage, the initial points to ...

3

The 2.25% is an annual effective rate (same as APY) e = 0.0225 Converting the annual effective rate to a monthly rate r = (1 + e)^(1/12) - 1 = 0.00185594 Compounding the principal, with n = 24 10000 (1 + r)^n = 10460.2625 Compounding the payments, with d = 100 and payment at month-end. fv = (d ((1 + r)^n - 1))/r = 2451.9379 Adding together 2451.9379 ...

3

(1+return)/(1+inflation)-1 would be more accurate (it discounts each year's return by the level of inflation), but your formula is often used as an easy estimate for small levels of inflation: (1.11 / 1.015) - 1 = 9.36% which is fairly close to the 9.5% you use. To convert to monthly raise it to the 1/12 power: (1.11 / 1.015)^(1/12) - 1 = 0.748% which ...

3

Your formula gives you daily compounding, assuming the annual interest rate was calculated on 360 days (a slightly shorter ‘year’ than a natural year, but not unheard of in the finance industry). If K15 is your ‘annual’ interest rate, K15/360 is your daily interest rate. (If you have a 10% rate, K15 should be 0.1, not 1.1. The extra 1 makes the interest ...

2

There is no ready made forumulae. Put the Deposit in cell B3 as 10,000,000 Put the rate of interest in Cell B2 as 2% [Format this as Percnetage] In Cell c3 write the formulae as =B3*B2*3/12 [Interest for 3 months till Dec] In cell B4 write =B3+C3 [Principal as of 1st jan 13 and in cell C4 as =B4*B2 [Interest for full years] In Cell B5 wirte = B4+C4 [new ...

2

The current Dow divisor is in Historical Divisor Changes. The OpenOffice GetQuote function offers fields for current dividend either in dollars or yield.

2

The solution to this problem is somewhat like grading on a curve. Use the consumption ratio multiplied by the attendance (which is also a ratio, out of 100 days) to calculate how much each person owes. This will leave you short. Then add together all of the shares in a category, determine the % increase required to get to the actual cost of that category, ...

2

Why use spreadsheets rather than writing your forms and formulas directly in a programming lanuage? Because you've got better things to do than reinvent the wheel, right? Same answer. ===== clarification, since the point apparently wasn't clear: Using a spreadsheet means you're writing and organizing and maintaining the formats and formulas yourself. ...

2

Since this is a cooperative I'm guessing your partners may want to be able to view the books so another key point you may want to consider is collaboration. Who is going to keep the "master" .xls file? Where do they go to get the latest file and how do you ensure that you're not making conflicting or duplicated entries or possibly losing data when you ...

2

Assuming the rate is 4.35% nominal, compounded monthly, in Excel the formula would be =PMT(0.003625,360,275000) resulting in -\$1,368.98 You can also use the mathematical formula here: http://www.financeformulas.net/Loan_Payment_Formula.html So r = 0.0435/12 = 0.003625 PV = 275000 n = 30*12 = 360 P = r (PV)/(1 - (1 + r)^-n) = 1368.98

Only top voted, non community-wiki answers of a minimum length are eligible