24
votes
How do I calculate monthly compound interest in Google Sheets?
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 ...
- 48.9k
17
votes
Do bank use the same math formula to calculate the mortgage amortization table?
Banks don't necessarily use the same formula, but in most countries they must disclose the effective interest you'd be paying (which may vary from the nominal interest due to extra charges and ...
- 155k
13
votes
How do I calculate monthly compound interest in Google Sheets?
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 ...
- 131
7
votes
Help me understand and calculate credit union interest in Google Sheets
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 ...
- 171
6
votes
Do bank use the same math formula to calculate the mortgage amortization table?
The mathematics on which the usual formula is based is that the sum of the payments d, each discounted to present value (PV) by 1/(1 + r)^k, should equal the initial (present value) value of the loan ...
- 9,377
5
votes
Clarify Microsoft's explanation of MIRR
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 ...
- 126k
4
votes
Accepted
Excel calculating interest rate on loan
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 ...
- 9,377
4
votes
Get Revenue Details in Google Sheets using Google Finance
Use this formula in Google Sheets:
=IMPORTXML("https://www.google.com/finance/quote/TSLA:NASDAQ","//table//tr[contains(@class,'roXhBd')]")
This will bring up a list of the company'...
- 41
3
votes
How do I calculate portfolio standard deviation in Excel?
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) =...
- 126k
3
votes
Accepted
How to calculate the number of months until a loan is paid off (given principal, APR and payment amount)?
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 ...
3
votes
Accepted
XIRR not working for this dataset. Any other method to calculate rate of return?
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)^(...
- 9,377
3
votes
Accepted
Excel formula for calculating interest rate of earning
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, ...
- 1,262
3
votes
Accepted
Building Marcus Savings Calculator in Excel
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
...
- 9,377
3
votes
Accepted
How to adjust for long term inflation in an investment plan
(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 /...
- 126k
3
votes
Accepted
Monthly Compound Interest calculation
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 ...
- 9,217
3
votes
Accepted
Is the S&P500's excess return above Treasuries after investing monthly for forty years really 3.6% annualized?
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…)...
- 161
3
votes
Accepted
Mortgage/annuity spreadsheet formula question
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. ...
- 129k
3
votes
Accepted
How can I track *complex* forex trades so I have an idea of my profit vs loss over time?
The solution to tracking trade history with different quantities is to use lots.
Say your first order was to buy 100 EUR (paid with some amount of USD). For accounting purposes, we can place that buy ...
- 198
2
votes
Why use accounting software like Quickbooks instead of Excel spreadsheets?
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.
=====
...
- 39.8k
2
votes
Why use accounting software like Quickbooks instead of Excel spreadsheets?
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 ...
- 419
2
votes
Is this how you do Currency Conversion in excel?
Your conversion is correct. Using excel doing the same conversion multiple times should be quite simple.
- 1,199
2
votes
Annual compounding with monthly payments
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 ...
- 9,377
2
votes
Accepted
Determining the interest rate necessary to retain a certain balance after sixty monthly withdrawals
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 ...
- 9,377
2
votes
Daily Interest Monthly Compounding Multiyear
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 "...
- 9,377
2
votes
How do I convert a 4% per annum interest rate, compounded semi-annually, to a monthly rate?
For this type of problem, it is often easier to convert from one rate to another through a third standard interest rate. One good candidate for this intermediate rate is what, here in Canada, is ...
- 4,284
2
votes
Accepted
Compare my variable portfolio performance with S&P 500?
To compare your performance with the S&P 500, I think you have to work out what you would have got at the end of the year if you had simply invested everything in the S&P 500 at the time you ...
- 9,079
2
votes
Accepted
IRR Calculation with multiple investments
IRR should work fine since you use the same time period for the cash flows - just use the total amount for each "year" as your cash flows.
MIRR takes into account the interest rate at which you can ...
- 126k
2
votes
Accepted
How can I calculate the annualised growth rate for an irregular investment?
Recapping your method with a simple example
initialvalue = 1000
m1start = 100
m2start = 100
m3start = 100
v3end = 1500
=RATE(3,-100,-1000,1500,1)
0.0528704
The calculated rate is 5.29% per period.
...
- 9,377
2
votes
Compound daily interest with monthly deposits over x amount of years in excel
The future value function in Excel is detailed here: https://exceljet.net/excel-functions/excel-fv-function
Assuming the nominal interest rate is 2% per annum compounded monthly.
Future value after ...
- 9,377
2
votes
Accepted
What's wrong with the XIRR calculation here?
You don't have a large enough positive transaction to balance the negative ones. For example deposits recorded as negative cash flows should be balanced against a positive balance (or positive ...
- 9,377
Only top scored, non community-wiki answers of a minimum length are eligible