Capitalised Interest
- Thread starter Ian D
- Start date
I can't help with the forumla; i'm a dunce when it comes to excel, but the final payout figure at the end of the project/loan is not a fixed thing, and would depend on how much you draw down during the project, and when you draw it down....so there could be several possibilities for what the final payout figure would be in reality.
Have you been able to successfully calculate it manually based on your anticipated drawdowns? Takes a lot of time, but a very worthwhile excersise.
Have you been able to successfully calculate it manually based on your anticipated drawdowns? Takes a lot of time, but a very worthwhile excersise.
Ian D said:
Hi Ian,
You want the "Future Value" worksheet function:
FV:
Returns the future value of an investment based on periodic, constant payments and a constant interest rate.
Syntax
FV(rate,nper,pmt,pv,type)
Rate is the interest rate per period.
Nper is the total number of payment periods in an annuity.
Pmt is the payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the pv argument.
Pv is the present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument.
Type is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.
eg:
=FV(10,1,0,500000)
Which gives you an answer of -$5,500,000.00
(with thanks to Excel Help cut and Paste)
Thanks
Joanna & Duncan
Thanks for the prompt responses - what a great forum - I have lurked for a while now and am always amazed at the willingness of people to help out with suggestions & advice in all areas.
I will try these options.
Basically loan would be for a 12 month period. interest calculated monthly, but no payments made. Monthly interest being added to the capital amount.
MMMMM Does that make sense?
Thanks again
Ian D
Joanna & Duncan
Thanks for the prompt responses - what a great forum - I have lurked for a while now and am always amazed at the willingness of people to help out with suggestions & advice in all areas.
I will try these options.
Basically loan would be for a 12 month period. interest calculated monthly, but no payments made. Monthly interest being added to the capital amount.
MMMMM Does that make sense?
Thanks again
Ian D
Here is the formula.
divide the annual interest rate by the payment period. (10% is 0.10)
In this case 0.10/12= 0.0083333
now add 1, you get 1.083333
put that to the power of the number of payment periods which is 12
The power of is the yx key on a sceintific or financial calculator and in excel it is the ^ symbol (shift + 6)
and you get an annual effective interest rate of 10.4713%,
so multiply 1.104713 by $500,000 and you get $552,356
In excel it would go like this
500,000 * (1+(.1/12)^12))
and you will get your answer
if you want to know how much you will owe in 24 months, just change the power of to 24
500,000 * (1+(.1/12)^24)) and you will find you will owe $610,195, such is the power of compunding.
I hope you are not too confused,
cheers,
RightValue
divide the annual interest rate by the payment period. (10% is 0.10)
In this case 0.10/12= 0.0083333
now add 1, you get 1.083333
put that to the power of the number of payment periods which is 12
The power of is the yx key on a sceintific or financial calculator and in excel it is the ^ symbol (shift + 6)
and you get an annual effective interest rate of 10.4713%,
so multiply 1.104713 by $500,000 and you get $552,356
In excel it would go like this
500,000 * (1+(.1/12)^12))
and you will get your answer
if you want to know how much you will owe in 24 months, just change the power of to 24
500,000 * (1+(.1/12)^24)) and you will find you will owe $610,195, such is the power of compunding.
I hope you are not too confused,
cheers,
RightValue
I tend to use seperate cells - split by month, so I can plug in diffferent draw-downs at various stages.
I simply use my projected cashflow spreadsheet, which has a few lines on it for the projects i'm doing. This allows me to be flexible in plugging in anticipated costs etc - not everything is straight line.
I simply use my projected cashflow spreadsheet, which has a few lines on it for the projects i'm doing. This allows me to be flexible in plugging in anticipated costs etc - not everything is straight line.
Just a curly one for you to ponder. You can capitalise interest upfront.
ie prepay interest in advance.
This is what I do on 2nd Mortgages & Equity Financing on construction.
eg.Say a 500K Drawdown to a borrower is required over 12 Months.
20% Pa Interest 5% Managment 10K Legals & other Fees
I plug in the Overall lend 1st =$684,564
Which calculates Prepaid interest $136,913.00
Est Fee Inc GST = $37,651.00
Est Legals & Val = 10,000.00
Gives 500K Total advance.
Juzz
ie prepay interest in advance.
This is what I do on 2nd Mortgages & Equity Financing on construction.
eg.Say a 500K Drawdown to a borrower is required over 12 Months.
20% Pa Interest 5% Managment 10K Legals & other Fees
I plug in the Overall lend 1st =$684,564
Which calculates Prepaid interest $136,913.00
Est Fee Inc GST = $37,651.00
Est Legals & Val = 10,000.00
Gives 500K Total advance.
Juzz
