Dear Friends,

I have prepared an excel sheet for calculation of EMI in accordance with the other attachment. Basically it is in relation to a loan, for which the ROI is 9.25% for the First Year, 10.25% for the next two years and 11% from 3rd Year onwards.

However, the principal amount does not come to a zero with the end of loan tenure and rather the borrower appears to pay excess.

Can you kindly review the file and let me have your comments / corrections.

Regards

DC

I have prepared an excel sheet for calculation of EMI in accordance with the other attachment. Basically it is in relation to a loan, for which the ROI is 9.25% for the First Year, 10.25% for the next two years and 11% from 3rd Year onwards.

However, the principal amount does not come to a zero with the end of loan tenure and rather the borrower appears to pay excess.

Can you kindly review the file and let me have your comments / corrections.

Regards

DC

- Leasing Factor With Variable Interest Rate
- Variable interest rate calculation
- Cash investment tracking when receiving variable interest rate
- Calculating equivalent annual % growth rates using multiple-year data
- Need Help By Tuesday -VARIABLE INTEREST RATE HELP HELP HELP
- Calculating Daily Interest Rate from APR
- Calculating average interest rate
- FVSCHEDULE should allow cell reference for interest rate schedule
- FVSCHEDULE should allow cell reference for interest rate schedule
- Given loan amount, emi and tenure, how to calculate interest rate?
- Calculate Interest RATE with Payments in Advance?
- Possible Variable in Rate Function
- Calculate the Interest Rate in a repayments schedule
- Possible Variable in Rate Function
- Calculating interest over time period with non-constant interest rate?
- Rate of Return calculation with series of equal payments
- Amortization schedule calc without interest rate known
- Calculate interest on an increasing balance with variable rate
- Comparing interest charges with fixed and variable interest rates.
- Calculating Daily Compound Interest with a Difference
- Calculate total amount due on a loan, including interest
- Mortgage Calculations With A Difference
- Calculate Compound Interest Rate Growth
- INTEREST RATE problem

I want to calculate compound, variable interest on an account consisting of

multiple deposits of irregular amounts and and irregular deposit dates.

It would be best if it could calculate on actual calendar dates, not just

30-day months/360 days. Also, it would be best if interest could be

compounded daily, rather than any larger period.

Does anyone happen to know a way to program this or a site that I might

purchase it from?

Thanks.

interest rates? E.G. Cash Funds.

I've created a spreadsheet to keep track of my modest share holdings and as a record of the past performance of closed out investments.

At present I am able to work out the exact % gain (or loss!) on an investment by taking the monetary change in value net of any fees and dividends, and expressing as a % of purchase value. Its a useful way to help work out where things have gone well, and where they haven't....

However, what I would really like to do is to work out the equivalent annual compound growth rate for any particular share, as this will enable me to make valid comparisons between investments over different time periods. So, for example, lets say I buy a share, hold it for 3 years and sell it for a total gain of 15%. You might suggest just dividing the % figure by the number of years to give an average gain of 5% per year.

However I think a truer reflection of performance is to work out compounded rates. To illustrate this further, lets take a standard interest bearing deposit account. If you had £100 in this account and earned 5% interest each year, you would end up not with £115, but with £115.76 after 3 years. The interest accumulated in all but the final year also earning interest in the subsequent years - the joy of compounding. While its easy to work out how funds accumulate over time with the rate of interest to hand, I don't have a method of performing the reverse calculation - extrapolating the interest rate from an end total and the time.

So for my aforementioned 15% over 3 years example, I can only guess the rate through trial and error, (its something just under 4.8% equivalent annual interest) - and that's just on paper. I wouldn't know where to begin when it comes to writing an excel formula to calculate this. The formula gets more complex still when you factor in time periods that are not whole years.

So, ideally, I would like to have a formula that calculates the time (in days, I guess?) an investment has been held from bought and sold dates (which I write in a format like so: 11-May-2009), and then uses this variable together with the total % gain (or loss) to work out the equivalent % rate of gain per year.

All of which will probably point to the fact that I'd earn more holding the money in a savings deposit account! If anyone can help out with this little headache, I'd most appreciate it. Thanks,

Andrew

I know how to use basic functions in Excel, but need a solution to what I consider a tricky problem.

Our ministry is running an investment contest for our 4rth graders and here's the scenario:

You invest a fixed dollar amount of say 5000.00 dollars on January 1st 2007. Then, every day you recieve a different percentage in dividends. Say 1.2 percent on the first day, 1.41 percent the next day, 0.9 percent the third day etc.

I need a way to enter each days interest rate and understand this will be a daily entry, BUT we need ONE FIELD which is our initial investment field to add each days interest, to that single field for all the days listed. I'm not sure if that makes sense.

It is kind of like a compound interest calculation for ONE FIELD (The Initial Principal), but with daily changing interest rates.

I was told to create an open ended ARRAY, but need this figured out by Wednesday to show the kiddos how all this stuff works.

Please Help.

Eddie

Thanks

compounded annual growth rate (CAGR) over 8 years Excel requires that I type

in the actual interest rate for all 8 years using the FVSCHEDULE function.

FVSCHEDULE does not allow me to use a cell reference in the interest rate

array. Which means if I want to model an investment with different interest

rates - -I have to manually change all eight years of the interest rate

schedule every time. I would like Excel to allow the FVSchedule function to

use cell references for the interest rate array. Or create a true CAGR

function.

----------------

This post is a suggestion for Microsoft, and Microsoft responds to the

suggestions with the most votes. To vote for this suggestion, click the "I

Agree" button in the message pane. If you do not see the button, follow this

link to open the suggestion in the Microsoft Web-based Newsreader and then

click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

compounded annual growth rate (CAGR) over 8 years Excel requires that I type

in the actual interest rate for all 8 years using the FVSCHEDULE function.

FVSCHEDULE does not allow me to use a cell reference in the interest rate

array. Which means if I want to model an investment with different interest

rates - -I have to manually change all eight years of the interest rate

schedule every time. I would like Excel to allow the FVSchedule function to

use cell references for the interest rate array. Or create a true CAGR

function.

----------------

This post is a suggestion for Microsoft, and Microsoft responds to the

suggestions with the most votes. To vote for this suggestion, click the "I

Agree" button in the message pane. If you do not see the button, follow this

link to open the suggestion in the Microsoft Web-based Newsreader and then

click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...heet.functions

Can anyone tell me how to solve for Interest RATE with 2 (or more) payments in advance. I know I can simply deduct the number of advance payments from the original PV but that does not return the same interest rate as I get on my HP calculator or by using T-Value (a financial amortization program). The issue is that when you have 2 payments in advance, standard amortization tables assume that you would have 1 payment of zero at the end of the cashflows before the residual (FV) becomes due (to make up for the fact that the last payment was paid at the start of the loan). I successfully created formulas that calculate PMT, PV and FV with multiple payments in advance but I keep getting "#NUM!" error messages when I try to solve for RATE. I turned on "Enable Iterative Calculations" under Excel Options/Formulas but none of the formulas I've tried work. If anyone can help, I would really appreciate it.

Thanks in advance.

Thane

correct, but I would like to introduce a variable. This is to have a holiday

for 6 months i.e. nil payment. To clarify:

I am trying to calculate the interest rate assumming that at the beginning

of a repayment structure there is an "holiday payment". "Holiday payment" -

This mean at the beginning of the term there would be as in this case 6

months where there is no payments. Monthy payments begin in Month 7 until

month 42 ie 36 Months.

Data Months Paid Data Holiday Data Total

Payment holiday in months 6 6

Months 36 36

Total Term of Loan 36 6 42

Monthly payments £24,000.00 £0.00 £20,571.43

Loan £705,270.00 £705,270.00 £705,270.00

Rate Calculated 13.69% -1200.00% 11.78%

Interest Paid £158,730.00 -£705,270.00 £158,730.00

Interest per Month £4,409.17 £3,779.29

Capital per Month £19,590.83 £16,792.14

£24,000.00 £20,571.43

The attached spreedsheet explains it better and shows the way in which the

Function is written.

Thank you for any help you can give.

I am trying to calculate the interest rate required for a loan of 200,000 with 60 monthly repayments of 6,500 to leave a residual of 60,000.

The rate is approximately 4.88%, calculated by trial and error in a 60 row worksheet, but is there a formula to calculate this figure direct?

Thanks in advance.

Bryan

correct, but I would like to introduce a variable. This is to have a holiday

for 6 months i.e. nil payment. To clarify:

I am trying to calculate the interest rate assumming that at the beginning

of a repayment structure there is an "holiday payment". "Holiday payment" -

This mean at the beginning of the term there would be as in this case 6

months where there is no payments. Monthy payments begin in Month 7 until

month 42 ie 36 Months.

Data Months Paid Data Holiday Data Total

Payment holiday in months 6 6

Months 36 36

Total Term of Loan 36 6 42

Monthly payments £24,000.00 £0.00 £20,571.43

Loan £705,270.00 £705,270.00 £705,270.00

Rate Calculated 13.69% -1200.00% 11.78%

Interest Paid £158,730.00 -£705,270.00 £158,730.00

Interest per Month £4,409.17 £3,779.29

Capital per Month £19,590.83 £16,792.14

£24,000.00 £20,571.43

The attached spreedsheet explains it better and shows the way in which the

Function is written.

Thank you for any help you can give.

Start date

End date

Initial deposit

I have a table that has the interest rate (APR) for each year. The dates will have a resolution of days. I need to calculate out how much money would be in an account on the end date if the initial deposit had been put in on the start date.

I can imagine a very brute force method of writing the script that does this - but does Excel have any nice built in functions for this?

I'm imagining something like:

Find interest earned from start date to end of year. I would need to extract out the exact year [YEAR(start date)] and then the number of days remaining in the year [something like ["12/31/" & YEAR(start date) - start date]]. I would then need to figure out the number of days in that year, so I guess that would look like: [("12/31/" & YEAR(start date - 1)) - "12/31/" & YEAR(start date))] and then I'd multiple the interest rate by the number of days remaining in the year divided by the number of days in the year. I'd multiply my initial deposit by that product.

Then I'd continue multiplying the products by the interest rates and I could keep on going but I think you get the idea.

This seems really brute force. Surely there is a better way.

I don't want to put the same annual payment in 20 cells to use IRR, and I want the number of years to be a variable, which IRR would not lend itself to. Rate should be able to do this task in a single cell, but the results I am getting are not accurate.

Basically I want to find the interest rate that will make the present value of the annual payments equal to the initial investment.

schedule when one variable (usually the interest rate) is unknown. I've

tried bankrate.com but its version can't handle a date prior to current date.

First template I downloaded from microsoft.com didn't have the ability to do

the calculation and print out a schedule. Have numerous clients with loans

that I need to have this schedule in order to apply principal/interest on

monthly payments.

the amount owed each month (amounts vary) and an interest rate that is tied

to the prime rate. No payment to be made until the end of the term.

The current variable rate is 6.25% and to break from the existing fixed rate contract will cost me $3,200.

Is there a formula in Excel that I can use to compare the two rates and loan amounts using the 2yr period?

i.e. $125,000 @ 8.50% for the next 2yrs with repayments of $300 every two weeks compared to

$128,200 @ 6.25% for the next 2 yrs with repayments of $300 every two weeks. Is it worth changing over?

Many thanks

Carl

I am calculating Daily compound interest on a Principal from a % p/a rate. I think I may have the right equation, however, I am not 100% confident with the answers.

The principal amount is $20,776.00, the interest rate is 4% p/a, the number of days is 14.

The equation I am using is = $20,776.00*(1+(4%/365))^14 = $20,807.90 I am pretty sure this is right.

However, where things go iffy is when I need to make deposits into the account. The 1st deposit is $43,505.00, this brings the balance to $64,312.90 and is made at the end of the 14 day period and the next is 28 days later. So my next equation look like this:

$64,312.90*(1+(4%/365))^28 =$64,510.53.

This goes on over another 10 months or so with days between deposits changing.

Am I doing this right? If not, how should I be doing it?

Thank you

I have a client who made a loan to a friend in August of 2003 of 5000 (principal) with an interest rate of 10%.

I can calculate the total amount due using =FV without problem. What I'd like is to break it down by year - so, for example, B1 has original principal (5000), b2 has after year 1, B3 year 2, and so on...

Any help would be great

Thanks!!

I have a mortgage problem that I simply cannot seem to figure out.

Scenario - I started doodling with buying a property to let and then started thinking about adding the "shortfall" between the required bond payment and the income generated by the property back onto the loan each month as the shortfall arises. Let's assume that the financial institution will "pay" this shortfall from a previously agreed upon additional amount available on the property - understand?

While there is a shortfall between the income generated by the property and the bond payment, insurance, taxes etc., the outstanding capital will obviously increase as each new month arises and each specific shortfall is added onto the outstanding capital (loan at the bank).

Once the shortfall turns into surplusses, the surplusses are in turn paid into the account until the bond is paid in full.

With a normal, say 15 year, bond, the interest payments and principal payments of the constant mortgage payment (assume fixed interest rate) nicely works out to a balance of 0 in month 180, but not in the scenario I describe.

What happens in the scenario I described above is that, if I keep the payment constant (=PMT(and the rest - calculated on the original term and the original investment) each month's deficit is added to the outstanding capital but the payment stays constant.

I want this payment to remain constant throughout the bond.

The outstanding capital gradually increases as deficits are added (the idea is not to have to pay anything out of my pocket into the bond while there is a shortfall).

Then, as the annual rent increases over the next few years, the deficit gets less and less until it turns positive. This positive figure is also added (actually, "deducted") to the outstanding capital and apart from the standard capital portion of the PMT function, lowers the outstanding capital even more.

All of the above causes the bons to be paid in full before the actual initial term is finished.

This is great until I tried getting the bond to actually finish on the date it was supposed to finish, despite all the additions (the deficits) to the outstanding capital and the subtractions (the surplusses) from the same.

I want to keep the payment the same throughout the bond term, assume a constant interest rate is charged and assume a constant net increase in rentals is achieved.

How do I calculate a constant payment for a given term and have it work out to 0 at the end of that term, given the aforementioned standards?

Let's hear your clever ideas, please!

Best regards

Harry

P.S. The attachment is very basic due to size restrictions. Please drag row A24:M24 down up to 240 months in column A (20 year bond)

Has anybody ever come up with a way to discover the a compound interest growth rate based on actual numbers... for example;

Jan05 - 75

Jan06 - 150

Jan07 - 290

Jan08 - 440

I have a long list of investments that I am working on for a colleague, and want to show the growth in interest rate. In addition, I have payment schedules from loans for which I want to do the same, although I imagine the formula will be the same.

Any suggestions?

Cheers,

Paul

My bank has agreed to loan $130,000 if I agree to pay a lump sum of $259,870 in 9 years. What is the annual interest rate.

I've done it on a finanical calculator and got 7.99% but I can't get the formula right on excel, it comes up with #NUM!

Thanks

No luck finding an answer? You could always try Google.