# Tag Info

24

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 ...

16

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 ...

13

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

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

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

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

To calculate the variance of a portfolio you also need the weights of each asset (ω(i)), and the correlation (or covariance) between each asset (ρ(ij) or COV(ij)). From there, the formula is: σ²(p) = ω²(1)σ²(1) + ω²(2)σ²(2) + ω²(3)σ²(3) + 2ρ(12)ω(1)ω(2)σ(1)σ(2) + 2ρ(13)ω(1)ω(3)σ(1)σ(3) + 2ρ(23)ω(2)ω(3)σ(2)σ(3) If you have covariances ...

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

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

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 ...

3

Self-answer—how awfully embarrassing, I had a bug in how I was handling dividends, in both the Google Sheets spreadsheet and in the webapp, in JavaScript. (I was basically not reinvesting dividends…) With this correction, the result is better: the excess return, investing the CPI every month, over the last forty years has been 5.8%. I noticed that the ...

3

Now I try to cumulate the interest (for 1 month) with following formula: CUMIPMT(annualRate/12, loanYears*12, loanReceived, 1, 2, 0) - I receive 237 That formula is calculating the first two months. That is why the answer, 237, is about double the 119 from the first formula.

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

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

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

Your conversion is correct. Using excel doing the same conversion multiple times should be quite simple.

2

This calculation arrives at the correct answer. However, it uses the formula for an annuity due. This means the payments are made at the beginning of the month and the last month of the 10 year period has interest accrued. See the section, Calculating the Future Value of an Annuity Due. The rate is given as an effective rate. annual effective rate = 12% ...

2

You need to solve the money-weighted return equation. It cannot be expressed as a formula for the interest, but it can be solved numerically as shown here. Using the OP's figures, with monthly withdrawals of \$100,000. The summation for the withdrawals can be replaced with the standard annuity formula. The resulting monthly return is converted to a ...

2

Here is a short explanation and example of daily interest and monthly compounding that will hopefully be helpful to you. Suppose a bank's interest rate is 5% nominal compounded monthly. The measure "monthly compounding" is the key to calculating the effective annual rate, from which can be calculated the daily rate. See https://en.wikipedia.org/wiki/...

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