Espirales. Revista multidisciplinaria de investigación científica, Vol. 6, No. 40

January - March 2022. e-ISSN 2550-6862. pp 22-36

DOI: https://doi.org/10.31876/er.v6i40.809

Optimization of renewable energy projects with Excel

when professional software is not available

Optimización de proyectos de fuentes renovables de energía con Excel

cuando no se dispone de software profesionales

Lorenzo Alfredo Enriquez Garcia*

Luis Jerónimo García Faure*

José Rigoberto Muñoz Cargua*

Received: June 02, 2021

Approved: September 01, 2021

* PhD. In Electronics and Electrical and

Control Systems, Escuela Superior Politécnica

de Chimborazo-Researcher, Riobamba,

Ecuador, lenriquez@espoch.edu.ec

https://orcid.org/0000-0001-7300-8204

* Doctor in Technical Sciences, Professor -

Researcher of the Universidad de Oriente,

Santiago de Cuba, Republic of Cuba,

lgarcia@uo.edu.cu https://orcid.org/0000-

0003-1237-3915

* D. in Physics, Escuela Superior Politécnica

de Chimborazo, Riobamba, Ecuador.

jose.munoz@espoch.edu.ec,

https://orcid.org/0000-0002-2876-1237

Cite this:

Enríquez, L., García, L., Muñoz, J.

(2022). Optimization of renewable

energy projects with Excel when

professional software is not

available. Espirales. Revista

Multidisciplinaria de investigación

científica, 6(40), 22-36

Abstract

Sometimes, for the realization of profitability analysis and projects

with renewable energy sources, professional software is not available,

or the input and output parameters do not correspond to those

available or desired by the user, since the designer chooses the

parameters with which his algorithm has to operate. Many projects

such as the one presented in this document can be designed by

professionals and students with modest computer knowledge and

solved with the Excel application present in all Microsoft Office

packages. The example shown is aimed at determining the main

parameters of the preliminary project, performing the profitability

calculations and the sensitivity analysis of a hybrid wind-electric

system that must guarantee the base demand of an isolated grid. The

results are compared with those obtained with the professional

software HOMER Beta V.2.68.

Keyword:

Macro project parameters; Hybrid systems; Project

feasibility; Excel programming.

Optimization of renewable energy projects with Excel when professional software is not available

Espirales. Revista multidisciplinaria de investigación científica, Vol. 6, No. 40

January - March 2022. e-ISSN 2550-6862. pp 22-36.

Introduction

In many cases there is a scenario given by the demand and availability of energy, but

there are no tools to carry out a feasibility study, determine the fundamental parameters

and analyze the profitability of the project. Many professional softwares require

expensive licenses for their use, others, although they can be "free", need to be

installed in the PC, which makes their transfer and demonstration difficult; however, the

most difficult problem to solve is the non coincidence in many occasions of the input

and output parameters with which the softwares operate and those available and

desired by the user.

Taking a wind project as an example, the parameter that determines the energy that

can be produced in a time t is the wind speed, which as it is known, has random

behavior. The way in which this parameter is input differs depending on the software

used; it can be the hourly average speed, monthly average, annual average, it can also

be the distribution of two Weibull parameters, etc.), then from that information the

software through its internal tools determines the probability distribution of each speed.

The two-parameter Weibull distribution represents very accurately the probabilistic

behavior of wind speeds at many locations around the globe. It is given by:

)1()(

Resumen

En ocasiones, para la realización de análisis de rentabilidad y

proyectos con fuentes renovables de energía no se dispone de un

software profesional, o no se corresponden los parámetros de

entrada y de salida con los disponibles o deseados por el usuario

pues el diseñador escoge los parámetros con los cuales ha de operar

su algoritmo. Muchos proyectos como el que se presenta en este

documento pueden ser diseñados por profesionales y estudiantes

con un modesto conocimiento de computación y resueltos con la

aplicación Excel presente en todos los paquetes de Microsoft Office.

El ejemplo mostrado tiene como objetivo la determinación de los

principales parámetros del anteproyecto, realizar los cálculos de

rentabilidad y el análisis de sensibilidad de un sistema híbrido eólico-

electrógeno que debe garantizar la demanda base de una red

aislada. Se comparan los resultados con los obtenidos en el software

profesional HOMER Beta V.2.68.

Palabra clave:

Parámetros macro del proyecto; Sistemas híbridos;

Factibilidad de proyectos; programación con Excel.

Lorenzo Alfredo Enriquez Garcia, Luis Jerónimo García Faure, José Rigoberto Muñoz Cargua

Espirales. Revista multidisciplinaria de investigación científica, Vol. 6, No. 40

January - March 2022. e-ISSN 2550-6862. pp 22-36

In which: k and c are the shape and scale parameters respectively, whose values can be

determined by different ways: empirical methods from mean velocity, least squares and

others (Khan et al., 2015).

For projects of some importance, average hourly velocities are normally taken

throughout the year, i.e. 8760 measurements and classified by ranges, e.g.: for group

, those between 0.5 and 1.4 m/s: for group v

, those between 1.5 and 2.4 m/s and so

on.

If n velocity measurements are made, the probability of a velocity i is given by:

In which, m

is the number of measurements (or hours) corresponding to the speed i and

n the number of total measurements made. It is evident that the sum of probabilities is

1 (100%). If wind speeds are measured for 8760 hours of the year, the time that each

probability is fulfilled is given by:

The energy developed by a wind generator during a time t

can be determined by:

In which: P

is the useful power developed by the wind generator at wind speed v

Generally, wind machines operate within a speed range from the starting speed v

the maximum or cut-off speed v

. Within that speed range, each machine has a

characteristic power curve, which may be as shown in Figure 1.

Figure 1.

Vestas V-82 wind turbine features

Source:

Authors

Knowing the probabilities of occurrence of each speed and the power curve of the

turbine, it is possible to accurately determine the total energy that the turbine can

develop in the year:

( )

)2(

( )

)3(8760 hvpt

×=

)4(kWhtPE

iii

×=

)5(/)()(8760 akWhvpvPE

iiT

×=

Optimization of renewable energy projects with Excel when professional software is not available

Espirales. Revista multidisciplinaria de investigación científica, Vol. 6, No. 40

January - March 2022. e-ISSN 2550-6862. pp 22-36.

Since discrete values of v are generally taken

, then the energy can be approximately

determined by:

The energy produced by the machine is used to obtain other project parameters.

It can be seen how important it is to know precisely the probability distribution of

velocities, which can be obtained in different ways depending on the input data.

Materials and methods

For the individualized solution of the programs on renewable energy sources it is

proposed to use the Microsoft Excel application for different reasons:

× It is present in all Office packages.

× It is easy to implement, correct, display.

× Unlike other programs, it is not necessary to know the programming instructions

by heart because they are written by making references to cells.

× It has virtually all the plug-ins needed to design any software.

× With a little skill you can give the program a professional feel.

The proposed example has been developed for the interactive solution of a wind-

electric hybrid power generation project connected to an isolated grid in order to

determine the macro parameters that guarantee the energy demand and the

profitability of the project.

The main source of energy is wind power, backed up by a generator set of several

machines whose number is a parameter to be optimized. The maximum power of the

generator set must be able to support the maximum hourly demand of the system so

that there are no voltage dips due to a lack of wind power. Depending on the available

wind energy, the wind farm contributes to the system and the genset reduces its

production with the consequent fuel savings; the system can support all the energy

produced by the wind farm. The power of the genset, the power and number of

machines in the wind farm, the wind parameters at the reference (measurement) height

k and c and the average air density at the height of the turbine shaft are given as variable

data.

For the calculations of the capital cost of the wind farm and the genset it is convenient

to use some method of cost estimation, such as those of the six-tenths theory or others,

in this case the parametric equation developed for Latin American countries as a

( )

××=

iiT

akWhvpvPE )6(/)(8760

Lorenzo Alfredo Enriquez Garcia, Luis Jerónimo García Faure, José Rigoberto Muñoz Cargua

Espirales. Revista multidisciplinaria de investigación científica, Vol. 6, No. 40

January - March 2022. e-ISSN 2550-6862. pp 22-36

function of the power, number, and hub height of the machines by Enriquez and Garcia

(Enriquez and Garcia et al., 2016) is used for the wind farm.

$ (7)

A fraction of the annual capital cost, typically between 5%-10%, is taken for the

operation and maintenance cost.

For the genset, the capital cost is estimated as a function of power using the six-tenths

theory with exponent 0.72 (Randall & Whitesides, 2012).

For the operation and maintenance cost, a fraction of the annual cost of the genset is

taken, generally between 5%-10% plus the cost of fuel consumed, for which the specific

consumption (kg/MWh) and the price ($/kg) are given. These costs can then be analyzed

as sensitivity parameters (Roqueñí et al., 2008).

With this information it is possible to determine:

× Total energy produced by the wind farm and the generator set.

× Wind farm and genset capacity factor.

× Generating set working hours per year.

× Genset fuel consumption.

× Cost of fuel consumed.

× Net present value of the project.

× Levelized cost of energy (COE) ($/kWh).

× Kg of CO

, SO

, NO

, CO going into the atmosphere.

× Perform sensitivity analyses on the influence of different factors on profitability.

The main calculations are performed in two linked Excel sheets. The first one is divided

into three main blocks, figure (2):

× Data entry of wind parameters.

× Turbine and genset data input.

× Results block.

In the wind data block, the parameters k, c and ρ are entered at the measurement height

(reference height Zref) and the terrain roughness height (Burton et al., 2002).

The second block provides the parameters of the turbines: type, maximum power and

number, hub height and the average value of the expected total efficiency due to wind

farm arrangement losses (Manwell & McGowan, 2009); the nominal power of the genset,

which is the firm or base power to be guaranteed and the number of generators that

make up the genset; the specific consumption and the price of each kg of fuel are also

given.

73,1924,0675,0

02,18 ZNPC

×××=

)8(($)0001

72.0

PNC

××=

Optimization of renewable energy projects with Excel when professional software is not available

Espirales. Revista multidisciplinaria de investigación científica, Vol. 6, No. 40

January - March 2022. e-ISSN 2550-6862. pp 22-36.

The Weibull shape parameter k does not vary practically when passing from the

reference height to the turbine hub height because the velocity frequency is the same

at both heights, but the scale parameter c is very sensitive to the height variation and

the surface roughness of the terrain Z ,

so its value must be corrected for the turbine

hub height (Villarrubia, 2007). It is shown to be given by:

In which:

Z - Turbine hub height (m)

- Roughness height

The energy correction factor (equation 6) is also determined for the effect of density

change due to the altitude of the site (Streeter et al., 2000). It is given by:

Being:

- Density of on site

1.225 - Density of air at normal standard conditions

Then, in the third block in column (A) the wind speeds from 1 to 25 m/s are placed,

which is normally the limit or cut-off speed of the turbines work; in column (B) the

Weibull equation (1) is applied with the value of c corrected in (8) to determine the

probability of each speed, but if the information that is available is the number of hours

that each speed m is fulfilled

, then the probabilities are determined by equation (2);

In column (C), the values of the turbine power corresponding to each wind speed are

entered, and then, in column (D), the energy generated by the wind farm for each wind

speed is determined. The number of turbines, the wind density correction factor and

the total efficiency of the wind farm must be taken into account:

The wind energy captured by the farm during the year is determined by adding the

values of the energies corresponding to each speed (sum of column D):

The park capacity factor is given by:

(12)

)9(

×=×=

cconstcc

ref

refrefZ

)10(

225,1

)11()()(.8760 kWhvpvPFNFENE

iitiEOi

××××=×××=

)11(/

EOiEO

akWhEE

×××

8760

Lorenzo Alfredo Enriquez Garcia, Luis Jerónimo García Faure, José Rigoberto Muñoz Cargua

Espirales. Revista multidisciplinaria de investigación científica, Vol. 6, No. 40

January - March 2022. e-ISSN 2550-6862. pp 22-36

In column (E) the energy demanded in each time interval is determined (in a similar way

as it was done with the energy produced by the turbines), the total sum of the column

equals the total energy demanded during the year. The differences between the cells

in column E and D give in column F, the deficit of wind energy in each interval to be

supplied by the genset. If the opposite occurs, i.e., when wind power exceeds demand,

then it is counted as excess energy produced, which if the system is an interconnected

grid can be absorbed. With this information it is possible to estimate if there is a deficit

or excess with respect to the base power and increase or decrease the number of

turbines, this can be another sensitivity parameter.

(13)

Excel provides the necessary logic functions to determine whether there is a deficit or

excess of energy. In column (F) the energy demand is compared to the wind potential

energy with the function ¨Si¨. That is:

=SI(Demand >E

EO,

(Demand-E

EOi

),(0)) : (14)

In column (H) the hours that the genset works when there is a wind energy deficit are

determined, this allows then to calculate the energy produced and the fuel consumed.

A function similar to the one given above can be used.

=SI(E

GEi

> 0,(8760*p(v

),(0))) (15)

These equations, like all the previous ones, are written by making references to the cells

where the variables are located.

The energy developed by the genset when there is a deficit of wind energy is

determined in column (I).

The total energy generated in the year by the genset is obtained by adding column (I)

The capacity factor of the genset is determined by:

(17)

The fuel consumption of the genset is given in column (J) by:

The sum of column (J) gives the annual fuel consumed.

The second sheet is composed of four blocks, figure (3), in the first one the data from

sheet 1 are imported following the Excel data import procedure; in the second block

other data are entered that can be variables in the problem: % allocated for operation

and maintenance, annual interest rate, electricity sales rate, years of project life, specific

fuel consumption and unit cost of fuel.

tiEOi

PNP ××=

)16(1000/ MWhtPE

iGEiGEi

×=

8760×

)18(/ hkgtEG

iGEii

×=

Optimization of renewable energy projects with Excel when professional software is not available

Espirales. Revista multidisciplinaria de investigación científica, Vol. 6, No. 40

January - March 2022. e-ISSN 2550-6862. pp 22-36.

Next, in the third block, preliminary calculations of capital costs, maintenance and

operating costs, and residual values of the wind farm and the genset are made (Blank &

Tarquin, 2006):

Annual cost of operation and maintenance of the wind farm

($/a) (19)

- Fraction of park cost (2-4 %)

N- Years of project life

Cost of operation and maintenance of the generator set

(20)

- Fraction of the value of the energy produced by the genset (4-6%)

- Annual energy produced by the generator set

- Electricity tariff

In addition to the cost of operation and maintenance, the cost of fuel must be taken

into account in generator sets.

To estimate the amount of fuel consumed, the specific consumption and the energy

generated are used as data:

(21)

To determine the cost of fuel, the unit cost is used:

(22)

To determine the profitability of the project, economic-financial evaluation criteria are

suggested that use asset flows discounted over time. Since the necessary information is

available to determine all costs, energy produced and income, any of the most

commonly used criteria can be applied: Net Present Value, Life Cycle Cost, Levelized

Cost of Energy and others. (Fernández, 2012). In this example the NPV is applied, it is

given by:

(23)

Where:

and C

- represent the capital costs of the wind farm and generator set.

rEO

and V

rGE

- represent the residual value of the fleet and genset after the useful life

period has expired. This is usually 10-25% of the initial cost.

CF - Cash Flows. It is the result of the income from electricity sales and the expenses

(operating, maintenance and fuel costs) during the years (n) of the project's life. For this

case, all cash flows are considered to be equal.

i - Interest rate to be paid annually for the project financing, normally between 6%-10%.

FCC

EOC

EOEO

aTEFCGEC

eGEGEom

/$××=

akgakWhEkWhkggG

/)/()/( ×=

aakgGkgCC

/$)/()/($ ×=

( )

rGErEOGEEO

VVCCVPN

++-+=

)()(

Lorenzo Alfredo Enriquez Garcia, Luis Jerónimo García Faure, José Rigoberto Muñoz Cargua

Espirales. Revista multidisciplinaria de investigación científica, Vol. 6, No. 40

January - March 2022. e-ISSN 2550-6862. pp 22-36

The professional software HOMER uses in its software the Net Present Cost (NPC).

(Beiter et al., 2016); (Oyarzo, 2008).

In the spreadsheet, after having the NPV result with the initial values assigned to each

variable, sensitivity analyses are performed with the selected variables, keeping the

other variables constant. You can have separate graphs, which can then be merged into

a single graph, called a spider graph. (Watson, 1995).

Figure 2 shows the behavior of the NPV when varying the interest rate in the range of

3% to 8% for a given project, the initial (equilibrium) value was 6% with the rest of the

variables, which yielded an NPV of $25.3.10

Note the marked influence of this factor on

profitability.

Figura 2.

Sensitivity of NPV to interest rate.

Source:

Authors

To determine the amount of gases that pollute the atmosphere, it is necessary to have

as information the amount of fuel consumed per year and its chemical composition,

then by analyzing the combustion residues, the remaining fraction of each component

per kg of fuel combusted is determined (Unnasch et al., 2001).

Results

The system is composed of 5 VESTAS generators of 1650 kW power and a generator

set composed of five generators of 800 kW power, for a total of 4 MW which is the

maximum load of the system. Figure 1 shows the input data and the calculations of wind

energy produced, energy supplied by the genset, excess energy, which is the energy

produced by the wind generators above the demand; the annual fuel consumption is

also determined.

Optimization of renewable energy projects with Excel when professional software is not available

Espirales. Revista multidisciplinaria de investigación científica, Vol. 6, No. 40

January - March 2022. e-ISSN 2550-6862. pp 22-36.

In the second sheet, based on the previous results, the factors that determine the

profitability and the combustion gases that have an impact on the environment are

determined.

The above results were compared with those obtained by the HOMER professional

software when supplied with the same data in an isolated system. Table 1. The results

are shown. It is observed that in the energy parameters the difference is very small; in

the case of the generator set working hours there is apparently a very large difference,

this is because HOMER performs the calculation by dividing the annual energy

developed by the generator set (11 108 MWh/y) by the power of the set (4MW),

resulting in 2777 hours. In the proposed method, the number of hours is obtained by

adding the times that the genset contributes to the grid, i.e.:

The polluting gases are calculated by conventional methods based on the elemental

composition of the fuel, the coefficient of excess air and the fraction of carbon involved

in the formation of CO2 and CO. The divergence in these results can be given by the

coefficient of excess air, which in this case was taken as 30%, or in the criterion for fixing

the fraction of carbon in the formation of CO.

Table 1:

Comparison of results with those obtained by HOMER Beta V2.68

Gener

Wind

MWh/a

F.C.

E.O.

Gener

Elect.

MWh/a

F.C

GE.

Excess

Energy

MWh/a

Hours

Gener.

Elect.

Consumption

Combust.

(Kg/y)

HOMER

V2.68

Beta

30 971

42,9

11 108

31.7

7 132

2 777

3 152 450

HYBRID

UO V-1.

30 752

42,6

11 337

32.4

7 049

5 230

3 287 777

Difference

0,7%

2,2%

1,2%

4,1%

Products of combustion (kg/a)

Financial

evaluation

0:)(8760

>×=

iGE

Eparavph

Lorenzo Alfredo Enriquez Garcia, Luis Jerónimo García Faure, José Rigoberto Muñoz Cargua

Espirales. Revista multidisciplinaria de investigación científica, Vol. 6, No. 40

January - March 2022. e-ISSN 2550-6862. pp 22-36

CO2

SO2

NO2

LCC

VPN

COE

$/kWh

HOMER

V2.68 Beta

9 652

837

19385

212607

23827

65 719

736

---

0,08

HYBRID

UO V-1.

10 078

132

21 699

229 380

67 509

---

16 816

650

0,09

Difference

11%

Source:

Authors

Figure 3.

Preliminary data sheet and calculations

Source:

Authors

Figure 4:

Project profitability analysis

Optimization of renewable energy projects with Excel when professional software is not available

Espirales. Revista multidisciplinaria de investigación científica, Vol. 6, No. 40

January - March 2022. e-ISSN 2550-6862. pp 22-36.

Source:

Authors

Conclusions

This example demonstrates that with a modest knowledge of Microsoft's Excel application, it is

possible to design important programs for the study of renewable energy projects. In this case

a wind-diesel hybrid system was taken as an example, but it could have been wind-hydro or

any other. Comparison of the results with the well-known HOMER software indicates that the

accuracy and therefore the confidence level is high. By simply varying some input data to the

program (Sheet 1), all values change automatically, which facilitates sensitivity analyses.

..........................................................................................................

References

Beiter, P., Elchinger, M., & Tian, T. (2016). U.S

Department of Energy: 2016

Renewable Energy Data Book.

https://www.nrel.gov/docs/fy18osti/70

231.pdf.

Blank, L., & Tarquin, A. (2006). ENGINEERING

ECONOMICS. Sixth Edition (S. A. D. C.

Lorenzo Alfredo Enriquez Garcia, Luis Jerónimo García Faure, José Rigoberto Muñoz Cargua

Espirales. Revista multidisciplinaria de investigación científica, Vol. 6, No. 40

January - March 2022. e-ISSN 2550-6862. pp 22-36

C. V. McGRAW-

HILL/INTERAMERICANA EDITORES

(ed.)).

Burton, T., Sharpe, D., Jenkins, N., & Bossanyi,

E. (2002). WIND ENERGY

HANDBOOK.

Enriquez, L., Garcia, L., Oro, C., & Muñoz, J.

(2016). Parametric Estimate Cost of

Wind Farms in Latin America and the

Caribbean. IOSR Journal of Mechanical

and Civil Engineering, 13(04), 66-70.

https://doi.org/10.9790/1684-

13040646670.

Fernández, C. (2012). Between IRR and NPV

for project evaluation. Instituto

Pacifico, VII, 1-2.

Khan, J. K., Uddin, Z., Tanweer, I. S., Ahmed,

F., Aijaz, A., & Jilani, S. U. (2015). An

Analysis of Wind Speed Distribution

and comparison of five numerical

methods for Estimating Weibull

Parameters at Ormara , Pakistan.

EUROPEAN ACADEMIC RESEARCH,

II(11), 14007-14015.

Manwell, J. F., & McGowan, J. G. (2009).

WIND ENERGY EXPLAINED. Theory,

Design and Application. Second

Edition.

Oyarzo, H. (2008). Mathematical modeling to

evaluate energies for wind and wind-

diesel hybrid systems. TECHNICAL

SCHOOL OF AGRONOMISTS

DEPARTMENT OF RURAL

ENGINEERING.

Randall, W., & Whitesides, P. E. (2012). Process

Equipment Estimating by Ratio and

Proportion. Course Notes, PDH Course

G, 127, 1-8. www.PDHcenter.com.

www.PDHcenter.com

Optimization of renewable energy projects with Excel when professional software is not available

Espirales. Revista multidisciplinaria de investigación científica, Vol. 6, No. 40

January - March 2022. e-ISSN 2550-6862. pp 22-36.

Roqueñí, I., Roqueñi, N., Álvarez, J., & Mesa,

J. (2008). Introduction. Revista Clínica

Española, 208, 1-9.

https://doi.org/10.1016/s0014-

2565(08)71781-8

Streeter, V. L., Wylie, E. B., & Bedford, K. W.

(2000). Fluid Mechanics - Streeter - 9th

Edition.pdf (p. 749).

Unnasch, S., Browning, L., & Kassoy, E. (2001).

Refinement of Selected Fuel-Cycle

Emissions Analyses. Final Report, ARB

Contract 98-338.

Villarrubia, M. (2007). Wind Energy Manual,

CEAC Edition.

Watson, K. (1995). Sensitivity Analysis in

Outcome Evaluations: A Research and

Practice Note. Canadian Journal of

Program Evaluation/La Revue

Canadienne d'evaluation de

Programme, 10(2), 113-122.